(Resolvido) MySQL - problema ao adicionar chave estrangeira à tabela [

PhoenixWings · Agosto 7, 2010

Boa noite gente. Criei uma tabela chamada my_contacts e outra jobs e estou tentando inserir uma chave estrangeira na primeira sem êxito. Segue o código abaixo:

# ALTER TABLE my_contacts  
# ADD COLUMN id_prof INT NOT NULL AFTER birthday;  
# OK, 15 rows affected (0.08 sec)  
# 15  Duplicates: 0  Warnings: 0

1.  ALTER TABLE my_contacts  
   2. ADD FOREIGN KEY(id_prof) REFERENCES jobs(id_job);  
   3. 1005 (HY000): Can't create table 'gregslist.#sql-4a4_2' (errno: 150)

my_contacts é minha parent table, jobs a minha child table e id_job a minha primary key na tabela jobs. Como faço para adicionar a foreign key na tabela my_contacts? obrigado pela ajuda.

Editado Agosto 10, 2010 por Denis Courcy

Denis Courcy · Agosto 9, 2010

Oi, 'PhoenixWings'

Este problema é mais comum do que você pode imaginar.

Verifique se os atributos em ambas tabelas tem tipo tamanho e sinalização iguais.

Na tabela filha my_contacts coloque id_job com default 0.

Tente novamente e informe resultado.

PhoenixWings · Agosto 9, 2010

Verifique se os atributos em ambas tabelas tem tipo tamanho e sinalização iguais.
Na tabela filha my_contacts coloque id_job com default 0.
Tente novamente e informe resultado.

Dennis, eu estou estudando através de um livro e cheguei agora em subconsultas então não sei o que você quis dizer com sinalização. Também não entendi o que você quis dizer com inserir 0 como default se a linha está como Auto_increment. Eu tentei colocar Default 0 na linha assim:

CREATE TABLE jobs
(
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY DEFAULT 0, 
  profession VARCHAR(20)
) AS
  SELECT profession FROM my_contacts
  GROUP BY profession
  ORDER BY profession;

e obtive o seguinte erro: "invalid default value for id" já a outra tabela criei desta forma:

CREATE TABLE my_contacts (
  last_name varchar(30) ,
  first_name varchar(20) ,
  email varchar(50) ,
  gender char(1),
  birthday date ,
  profession varchar(50),
  location varchar(50),
  status varchar(20),
  interests varchar(100),
  seeking varchar(100)
);

e depois adicionei o id_contato e mais duas colunas:

ALTER TABLE my_contacts
ADD COLUMN city VARCHAR(20),
ADD COLUMN state CHAR(2);

ALTER TABLE my_contacts
ADD COLUMN id_contact INT NOT NULL AUTO_INCREMENT FIRST, 
ADD PRIMARY KEY(id_contact);

Editado Agosto 9, 2010 por PhoenixWings

Denis Courcy · Agosto 9, 2010

Oi, 'PhoenixWings'

Agora já sei o que você quer.

Faça assim:

CREATE TABLE jobs
(
  idjob INT(11) NOT NULL AUTO_INCREMENT, 
  profession VARCHAR(20),
  PRIMARY KEY (idjob)
);

CREATE TABLE my_contacts (
  id_contact INT NOT NULL AUTO_INCREMENT,
  last_name varchar(30) ,
  first_name varchar(20) ,
  email varchar(50) ,
  gender char(1),
  birthday date ,
  idjob INT(11) NOT NULL DEFAULT 0,
  location varchar(50),
  status varchar(20),
  interests varchar(100),
  seeking varchar(100),
  city VARCHAR(20),
  state CHAR(2),
  PRIMARY KEY(id_contact),
  CONSTRAINT `FK_MY_CONTACTS001` FOREIGN KEY (`idjob`) REFERENCES `jobs` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE              
);

Veja idjob em my_contacts ele tem a mesma estrutura que idjob em jobs.

Quando falei em sinalização falei no sentido de que um número inteiro pode ser representado no conjunto dos numeros naturais com a aplicação da cláusula UNSIGNED.

Sem esta cláusula ele pertencerá ao conjunto dos inteiros.

Matéria do 6º ano 1º grau - Conjuntos Numéricos.

PhoenixWings · Agosto 9, 2010

Obrigado pela atenção cedida Dennis. os códigos passados funcionaram contudo eu queria mesmo era inserir a chave estrangeira numa tabela que já estivesse criada pois parti do pressuposto que a tabela teria milhares de registros então não daria para apagar esta e criar uma outra. Existe a possibilidade de inserir a foreign key na tabela já criada com Alter? era isto que eu estava tentando fazer para a tabela my_contacts. Obrigado.

Denis Courcy · Agosto 10, 2010

Obrigado pela atenção cedida Dennis. os códigos passados funcionaram contudo eu queria mesmo era inserir a chave estrangeira numa tabela que já estivesse criada pois parti do pressuposto que a tabela teria milhares de registros então não daria para apagar esta e criar uma outra. Existe a possibilidade de inserir a foreign key na tabela já criada com Alter? era isto que eu estava tentando fazer para a tabela my_contacts. Obrigado.

Então faça assim:

ALTER TABLE my_contacts (
  ADD COLUMN  idjob INT(11) NOT NULL DEFAULT 0,
  ADD CONSTRAINT `FK_MY_CONTACTS001` FOREIGN KEY (`idjob`) REFERENCES `jobs` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE;

PhoenixWings · Agosto 10, 2010

Boa tarde. Eu inseri o código citado e mesmo assim não funcionou:

ALTER TABLE my_contacts 
  ADD COLUMN  idjob INT(11) NOT NULL DEFAULT 0,
  ADD CONSTRAINT `FK_MY_CONTACTS001` FOREIGN KEY (`idjob`) REFERENCES `jobs` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE;

Error 1452 (23000): Cannot add or update a child row: a foreign key constraint  fails(`gregslist` (result 2 when explaining filename `#sql-7bc-1`), CONSTRAINT `FK_MY_CONTACTS001` FOREIGN KEY ( `idjob`) REFERENCES `jobs` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE)

Esqueci de dizer que estou usando InnoDB (não sei se isso faz alguma diferença).

Denis Courcy · Agosto 10, 2010

Oi, 'PhoenixWings'

Você inseriu o código naquela tabela que enviei?

Se sim então ela já tinha a constraint e é por isso que está dando erro.

removi a constrint e o atributo e depois apliquei a atualização de tabela que passei no post anterior e funcionou sem erros.

Esqueci de dizer que estou usando InnoDB (não sei se isso faz alguma diferença).

Faz toda a diferença. Constraints só funcionam em tabelas do tipo Innodb.

PhoenixWings · Agosto 10, 2010

Oi, 'PhoenixWings'
Você inseriu o código naquela tabela que enviei?
Se sim então ela já tinha a constraint e é por isso que está dando erro.
removi a constrint e o atributo e depois apliquei a atualização de tabela que passei no post anterior e funcionou sem erros.

Eu já tinha criado as suas tabelas corretamente. Agora criei elas com um 2 na frente (só para não apagar a my_contacts e a jobs). Tentei apagar a foreign key criada na tabela my_contacts2 a fim de adicioná-la com ALTER TABLE (como você mostrou acima).

mysql> ALTER TABLE my_contacts2 DROP FOREIGN KEY idjob;
ERROR 1025 (HY000): Error on rename of '.\gregslist\my_contacts2' to '.\gregslist\#sql2-72c-2' (errno: 152)

Consegui Denis, muito obrigado! Segue abaixo o que eu fiz: 1º ) Criei as tabelas mas com 2 na frente para não precisar apagar a my_contacts e jobs:

mysql> CREATE TABLE my_contacts2
    -> (
    ->   id_contact INT NOT NULL AUTO_INCREMENT,
    ->   last_name varchar(30) ,
    ->   first_name varchar(20) ,
    ->   email varchar(50) ,
    ->   gender char(1),
    ->   birthday date ,
    ->   idjob INT(11) NOT NULL DEFAULT 0,
    ->   location varchar(50),
    ->   status varchar(20),
    ->   interests varchar(100),
    ->   seeking varchar(100),
    ->   city VARCHAR(20),
    ->   state CHAR(2),
    ->   PRIMARY KEY(id_contact),
    -> CONSTRAINT my_contacts2_fk FOREIGN KEY (idjob) REFERENCES jobs2 (idjob) ON DELETE CASCADE ON UPDATE CAS
CADE
    -> );
Query OK, 0 rows affected (0.02 sec)

2º ) Removi a constraint:

mysql> ALTER TABLE my_contacts2 DROP FOREIGN KEY my_contacts2_fk;
Query OK, 0 rows affected (0.06 sec)

3º ) Adicionei a constraint:

mysql> ALTER TABLE my_contacts2
    -> ADD CONSTRAINT my_contacts2_fk FOREIGN KEY(idjob) REFERENCES jobs2(idjob);
Query OK, 0 rows affected (0.06 sec)

4º ) Repeti o 2º passo, removi a coluna idjob e adicionei-a logo em seguida:

mysql> ALTER TABLE my_contacts2
    -> DROP COLUMN idjob;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE my_contacts2
    -> ADD COLUMN idjob INT NOT NULL DEFAULT 0 AFTER birthday;
Query OK, 0 rows affected (0.06 sec)

5º ) Por fim, adicionei a constraint de novo e usei DESC na tabela:

mysql> ALTER TABLE my_contacts2
    -> ADD CONSTRAINT my_contacts2_fk FOREIGN KEY(idjob) REFERENCES jobs2(idjob);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC my_contacts2;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id_contact | int(11)      | NO   | PRI | NULL    | auto_increment |
| last_name  | varchar(30)  | YES  |     | NULL    |                |
| first_name | varchar(20)  | YES  |     | NULL    |                |
| email      | varchar(50)  | YES  |     | NULL    |                |
| gender     | char(1)      | YES  |     | NULL    |                |
| birthday   | date         | YES  |     | NULL    |                |
| idjob      | int(11)      | NO   | MUL | 0       |                |
| location   | varchar(50)  | YES  |     | NULL    |                |
| status     | varchar(20)  | YES  |     | NULL    |                |
| interests  | varchar(100) | YES  |     | NULL    |                |
| seeking    | varchar(100) | YES  |     | NULL    |                |
| city       | varchar(20)  | YES  |     | NULL    |                |
| state      | char(2)      | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

Editado Agosto 10, 2010 por PhoenixWings

Denis Courcy · Agosto 10, 2010

...
Eu já tinha criado as suas tabelas corretamente. Agora criei elas com um 2 na frente (só para não apagar a my_contacts e a jobs). Tentei apagar a foreign key criada na tabela my_contacts2 a fim de adicioná-la com ALTER TABLE (como você mostrou acima).
mysql> ALTER TABLE my_contacts2 DROP FOREIGN KEY idjob;
ERROR 1025 (HY000): Error on rename of '.\gregslist\my_contacts2' to '.\gregslist\#sql2-72c-2' (errno: 152)
Só que eu não sei remover a constraint (se este procedimento for necessário antes de usar DROP na chave estrangeira).

Para remoção de chave estrangeira a sintaxe é:

DROP FOREIGN KEY fk_symbol

Nomes de chaves são importantes para uma hora destas. Quando eu criei a chave estrangeira eu dei um nome a ela. Lembra?

CONSTRAINT `FK_MY_CONTACTS001` FOREIGN KEY (`idjob`) REFERENCES `jobs` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE;

`FK_MY_CONTACTS001` é o nome que dei a esta chave. para eliminá-la use:

ALTER TABLE my_contacts 
DROP FOREIGN KEY `FK_MY_CONTACTS001`;

Nota: A chave deve ser eliminada antes de eliminar o atributo.

PhoenixWings · Agosto 10, 2010

Resolvi o problema como editado acima. Obrigado Denis!

Entrar

(Resolvido) MySQL - problema ao adicionar chave estrangeira à tabela [

Pergunta

Link para o comentário

Compartilhar em outros sites

10 respostass a esta questão

Posts Recomendados

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Estatísticas dos Fóruns