Erro 1005 ao criar tabela no MySQL

[SandraD]

Pessoal,

Sou (re)iniciante em MySQL e estou encontrando dificuldade para criar duas foreign key para uma mesma tabela, vejam se vocês podem me ajudar.

CREATE TABLE  IF NOT EXISTS CMF.NTZ_CT
( 
    CD_NTZ               smallint  NOT NULL,
    DCR_NTZ              char(30)  NOT NULL,
PRIMARY KEY  (CD_NTZ)
);

CREATE TABLE  IF NOT EXISTS CMF.CTGR_CT
( 
CD_NTZ_CTGR          smallint  NOT NULL ,
    CD_CTGR              smallint  NOT NULL ,
    DCR_CTGR             char(30)  NULL ,
PRIMARY KEY  (CD_NTZ_CTGR, CD_CTGR),
FOREIGN KEY (CD_NTZ_CTGR) REFERENCES CMF.NTZ_CT (CD_NTZ)
);

CREATE TABLE IF NOT EXISTS CMF.SUB_CTGR_CT 
(
  CD_NTZ_CTGR_S        smallint  NOT NULL ,
    CD_CTGR_S            smallint  NOT NULL ,
    CD_SUB_CTGR          smallint  NOT NULL ,
    DCR_SUB_CTGR         char(30)  NULL ,
PRIMARY KEY (CD_NTZ_CTGR_S, CD_CTGR_S, CD_SUB_CTGR)
);

ALTER TABLE CMF.SUB_CTGR_CT
ADD FOREIGN KEY (CD_NTZ_CTGR_S) REFERENCES CMF.CTGR_CT (CD_NTZ_CTGR);

[COLOR="Red"]ALTER TABLE CMF.SUB_CTGR_CT
ADD FOREIGN KEY (CD_CTGR_S) REFERENCES CMF.CTGR_CT (CD_CTGR);[/COLOR]

O erro ocorre no Alter Table em vermelho:

Error Code: 1005. Can't create table 'cmf.#sql-518_4' (errno: 150)

Li vários posts antes de postar a dúvida e acho que deve ser um erro bem simples mas não consegui encontrá-lo por isso peço ajuda aqui.

Desde já, muito obrigada a quem puder ajudar.

[fernando c]

olha.. testei e está td ok...

mas eu costumo fazer assim: 1. crio a tabela; 2. dou 1 refresh no banco; 3. crio nova tabela; 4. novo refresh.. etc..

só p/ via das duvidas: as tabelas são InnoDB?

"...Blue skies from pain.. can you tell a green field..

from a cold stell rail.. a smile from a veil?

do you think you can tell.."..

[SandraD]

Ainda não deu certo, Fernando.

Estou usando MySQL 5.5 e criando as tabelas no Workbench.

Quanto ao InnoDB... :s Ele não é o default pra esta versão do MySQL? Por via das dúvidas, depois q você me perguntou, coloquei

SET STORAGE_ENGINE=InnoDB depois do use mas ainda assim o erro persiste.

Continuo tentando aqui, achando a solução eu posto aqui ou, se tiverem mais alguma sugestão, será bem vinda.

Obrigada.

[DiF]

julgando... esse erro ... geralmente acontece quando há erros de chaves estrangeiras... ou indices.. sinceramente suas tabelas sao bem confusas nas nomenclaturas e na sua logica.

você usou smallint.. ? e nao há um campo auto increment? todo registro .. a melhor forma de ser criado é tendo um auto increment e ser int (11) .. porque digo 11? sao 11 digitos.. nao haveria problemas com falta de "espaço"

quando pus suas tabelas no mysql.. nao deixei de notao que nenhuma de suas tabelas possui uma chave estrageira. então é possivel que você tenha feito errado os indices referentes aos campos que recebem a chave primaria da outra tabela

[SandraD]

A fim de encontrar e entender o erro, criei um modelo das tabelas no Erwin e gerei o script para comparar com o meu script original. Havia uma série de diferenças: as tabelas eram todas primeiramente criadas e depois alteradas para se adicionar as chaves primárias e só depois as chaves estrangeiras. Também era dado um nome para cada foreign key. Então fui alterando o script gerado, de forma que se assemelhasse mais com o meu e finalmente encontrei o erro, que, como imaginei, era bem óbvio. Na última tabela havia uma foreign key composta e eu a estava declarando de forma separada.

ALTER TABLE CMF.SUB_CTGR_CT

ADD FOREIGN KEY (CD_NTZ_CTGR_S) REFERENCES CMF.CTGR_CT (CD_NTZ_CTGR);

ALTER TABLE CMF.SUB_CTGR_CT

ADD FOREIGN KEY (CD_CTGR_S) REFERENCES CMF.CTGR_CT (CD_CTGR);

Concordo com o usuário dif quando ele disse que os nomes estavam confusos para o post então reescrevi o script, simplificando os nomes para ficar mais claro e talvez poder ajudar alguém que esteja passando pela mesma dúvida.

Quando ao auto-increment field, eu havia omitido para não poluir mais o código e, quanto ao tamanho dos campos, está dentro das necessidades da minha aplicação.

Script corrigido:

use cmf;

CREATE TABLE Natureza

(

Codigo_da_Natureza INTEGER NOT NULL,

Descricao_da_Natureza CHAR(40) NOT NULL,

PRIMARY KEY (Codigo_da_Natureza)

);

CREATE TABLE Categoria

(

Codigo_da_Categoria INTEGER NOT NULL,

Descricao_da_Categoria CHAR(40) NOT NULL,

Codigo_da_Natureza INTEGER NOT NULL,

PRIMARY KEY (Codigo_da_Categoria,Codigo_da_Natureza),

FOREIGN KEY (Codigo_da_Natureza) REFERENCES Natureza (Codigo_da_Natureza)

);

CREATE TABLE Sub_Categoria

(

Codigo_da_Sub_Categoria INTEGER NOT NULL,

Descricao_da_Sub_Categoria CHAR(40) NOT NULL,

Codigo_da_Natureza INTEGER NOT NULL,

Codigo_da_Categoria INTEGER NOT NULL,

PRIMARY KEY (Codigo_da_Sub_Categoria,Codigo_da_Natureza,Codigo_da_Categoria),

FOREIGN KEY (Codigo_da_Categoria, Codigo_da_Natureza) REFERENCES Categoria (Codigo_da_Categoria, Codigo_da_Natureza)

);

Obrigada a todos pela ajuda.

[DiF]

Perfeito, é esse o espirito da coisa!

eu realmente nao tava entendendo porque nao havia um auto increment ali.. exatamente isso.. você esta complemente certa.. sempre declara o tamanho que necessita.. só e campos de texto que eu realmente dou uma folguinha de caracteres. enfim.. se você conseguiu resolver.. ótimo!

sinta-se livre de nos pedir ajuda de novo.

abraço