(Resolvido) Erro ao criar tabela

[Dauro Freitas]

Boa tarde pessoas,

Bom não tenho muito conhecimento com SQL mas estou trabalhando nisso.

Me deparei com um problema de difícil resolução ao meu ver.

Estou criando uma tabela instituição (inst) mas ela dá o errno:150.

Se alguém puder me ajudar as tabelas são essas:

CREATE TABLE IF NOT EXISTS `tip_inst` (

`cod_tip_inst` TINYINT(2) NOT NULL AUTO_INCREMENT ,

`desc_tip_inst` VARCHAR(20) NOT NULL ,

PRIMARY KEY (`cod_tip_inst`) )

ENGINE = InnoDB DEFAULT CHARSET=utf8

COMMENT = 'Tipos de instituição';

CREATE TABLE IF NOT EXISTS `UF` (

`cod_uf` TINYINT(2) NOT NULL AUTO_INCREMENT ,

`desc_uf` VARCHAR(30) NULL ,

PRIMARY KEY (`cod_uf`) )

ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Pais` (

`cod_pais` SMALLINT(3) NOT NULL AUTO_INCREMENT ,

`nom_pais` VARCHAR(50) NULL ,

PRIMARY KEY (`cod_pais`) )

ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `reg_inst` (

`cod_inst` MEDIUMINT(6) NOT NULL AUTO_INCREMENT ,

`cod_tip_inst` TINYINT(2) NOT NULL ,

`cnpj` VARCHAR(15) NULL ,

`nome_inst` VARCHAR(50) NOT NULL ,

`dat_fund` DATE NULL ,

`autoriz_divulg_dat` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Registra se o projeto tem autorização para divulgar a data de fundação' ,

`tel_inst` VARCHAR(15) NULL ,

`autoriz_divulg_tel` TINYINT(1) NOT NULL DEFAULT 0 ,

`fax_instituicao` VARCHAR(15) NULL ,

`autoriz_divulg_fax` TINYINT(1) NOT NULL DEFAULT 0 ,

`cel_inst` VARCHAR(15) NULL ,

`autoriz_ dilvulg_cel` TINYINT(1) NOT NULL DEFAULT 0 ,

`email_inst` VARCHAR(50) NULL ,

`site_blog_inst` VARCHAR(100) NULL ,

`autoriz_divulg_blog` TINYINT(1) NOT NULL DEFAULT 0 ,

`sede_prop_inst` TINYINT(1) NULL DEFAULT 0 ,

`logra_inst` VARCHAR(50) NOT NULL ,

`num_end_inst` MEDIUMINT(6) NOT NULL ,

`compl_end_inst` VARCHAR(10) NULL ,

`bair_inst` VARCHAR(30) NOT NULL ,

`cep_inst` INT(8) NOT NULL ,

`autoriz_divulg_end` TINYINT(1) NOT NULL ,

`cidade_inst` VARCHAR(30) NOT NULL ,

`cod_uf_inst` TINYINT(2) NOT NULL ,

`cod_pais_inst` SMALLINT(3) NOT NULL ,

`integr_inst` VARCHAR(200) NULL ,

`autoriz_divulg_integr` TINYINT(1) NOT NULL ,

`nom_resp` VARCHAR(50) NULL ,

`autoriz_divulg_nom_resp` TINYINT(1) NOT NULL DEFAULT 0 ,

`nom_art_resp` VARCHAR(50) NULL ,

`autoriz_divulg_nom_art_resp` TINYINT(1) NOT NULL DEFAULT 0 ,

`cpf_resp` INT(11) NOT NULL ,

`id_resp` VARCHAR(20) NOT NULL ,

`func_resp` VARCHAR(30) NULL ,

`autoriz_divulg_func` TINYINT(1) NOT NULL DEFAULT 0 ,

`tel_resp` VARCHAR(15) NULL ,

`autoriz_divulg_tel_resp` TINYINT(1) NOT NULL DEFAULT 0 ,

`cel_resp` VARCHAR(15) NULL ,

`autoriz_divulg_cel_resp` TINYINT(1) NOT NULL DEFAULT 0 ,

`email_resp` VARCHAR(50) NULL ,

`autoriz_divulg_email_resp` TINYINT(1) NOT NULL DEFAULT 0 ,

`logra_resp` VARCHAR(50) NOT NULL ,

`num_end_resp` VARCHAR(6) NOT NULL ,

`compl_end_resp` VARCHAR(10) NULL ,

`bairro_resp` VARCHAR(30) NOT NULL ,

`cep_resp` INT(8) NOT NULL ,

`cidade_resp` VARCHAR(30) NOT NULL ,

`cod_uf_resp` TINYINT(2) NOT NULL ,

`cod_pais_resp` SMALLINT(3) NOT NULL ,

`nom_cont` VARCHAR(50) NOT NULL ,

`tel_cont` VARCHAR(15) NULL ,

`e-mail_cont` VARCHAR(50) NULL ,

`flag_vincul_assoc` TINYINT(1) NOT NULL DEFAULT 0 ,

`nome_entidade_vinculada` VARCHAR(50) NULL ,

`flag_publ` TINYINT(1) NOT NULL DEFAULT 0 ,

`desc_publ` VARCHAR(100) NULL ,

`desc_aces_publ` VARCHAR(100) NULL ,

`flag_intere_divulg_publ` TINYINT(1) NOT NULL DEFAULT '0' ,

`flag_prod` TINYINT(1) NOT NULL DEFAULT '0' ,

`desc_prod` VARCHAR(100) NULL ,

`desc_aquis_prod` VARCHAR(100) NULL ,

`flag_inter_divulg_prod` TINYINT(1) NOT NULL DEFAULT '0' ,

`flag_inter_divulg_fot` TINYINT(1) NOT NULL DEFAULT '0' ,

`hist_inst` VARCHAR(1500) NULL ,

`ent_mant_esc` VARCHAR(50) NULL ,

`flag_divulg_nom_mant` TINYINT(1) NOT NULL DEFAULT 0 ,

`site_mant` VARCHAR(100) NULL ,

`flag_divulg_sit_mant` TINYINT(1) NOT NULL DEFAULT '0' ,

`qtd_alun` MEDIUMINT(6) NULL ,

`flag_grup_pesq` TINYINT(1) NULL DEFAULT '0' ,

`desc_tem_pesq` VARCHAR(100) NULL ,

`data_cad` DATE NOT NULL ,

`resp_cad` VARCHAR(15) NOT NULL ,

`dat_liber_pub` DATE NOT NULL ,

`resp_pub` VARCHAR(15) NOT NULL ,

`situa_pub` ENUM('Autorizado','Não Autorizado','Em Avaliação') NOT NULL ,

PRIMARY KEY (`cod_inst`) ,

INDEX `cod_tip_inst` (`cod_tip_inst` ASC) ,

INDEX `cod_uf` (`cod_uf_inst` ASC, `cod_uf_resp` ASC) ,

INDEX `cod_pais` (`cod_pais_inst` ASC, `cod_pais_resp` ASC) ,

CONSTRAINT `cod_tip_inst`

FOREIGN KEY (`cod_tip_inst` )

REFERENCES `reg_tip_inst` (`cod_tip_inst` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `cod_uf`

FOREIGN KEY (`cod_uf_inst` , `cod_uf_resp` )

REFERENCES `reg_uf` (`cod_uf` , `cod_uf` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `cod_pais`

FOREIGN KEY (`cod_pais_inst` , `cod_pais_resp` )

REFERENCES `reg_pais` (`cod_pais` , `cod_pais` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB DEFAULT CHARSET=utf8;

COMMENT = 'Cadastra instituição\n';

Eita tabela grande.

Bom fica aqui meu pedido de ajuda.

Fico no aguardo....

[Denis Courcy]

Oi, 'Dauro Freitas'

Vou ser direto e reto.

1) Não use tinyint(1) o tipo tinyint possui 2 bytes e sempre será criado assim. Use Byte.

2) Se você deseja usar auto_increment então use unsigned. Não existe auto_increment abaixo de zero. E quando você usa unsigned, você estará dando ao campo a possibilidade de alcançar todos os números disponúiveis para ele.

3)As tabelas pais, tip_int e uf não contém erros que impeçam a criação delas.

4)A tabela reg_inst, se criada sem as constraints, está ok, mas está desnormalizada. Há muita duplicidade de informação.

5) Se você está criando constraints não há a necessidade de criar os indices

INDEX `cod_tip_inst` (`cod_tip_inst` ASC) ,

INDEX `cod_uf` (`cod_uf_inst` ASC, `cod_uf_resp` ASC) ,

INDEX `cod_pais` (`cod_pais_inst` ASC, `cod_pais_resp` ASC) ,

pois eles serão criados pela cláusula FOREIGN KEY.

6) Voce está tentando fazer mágica na criação das constraints.

6.1) O nome de uma constraint não deve ser o nome de um atributo nem de um indice já existente.

Voce criou o índice e depois tentou criar a constraint com o mesmo nome. (Todas as três).

Eu troquei os nomes das constraints para FK_reg_inst_001, FK_reg_inst_002, ..., e FK_reg_inst_005.

6.2) A primeira constraint

CONSTRAINT `cod_tip_inst`
    FOREIGN KEY (`cod_tip_inst` )
    REFERENCES `reg_tip_inst` (`cod_tip_inst` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

Está referenciando uma tabela que não existe (reg_tip_inst). Você criou a tabela (tip_inst). Troque o nome da constraint e ela passará. 6.3) A segunda constraint

CONSTRAINT `cod_uf`
    FOREIGN KEY (`cod_uf_inst` , `cod_uf_resp` )
    REFERENCES `reg_uf` (`cod_uf` , `cod_uf` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

Você está cometendo dois erros aqui. o primeiro é o mesmo erro da constraint anterior. Você está se referindo a uma tabela que não existe. (reg_uf) o correto é (uf). O segundo, você está colocando dois atributos(`cod_uf_inst` , `cod_uf_resp`) como foreign key e na referência a tabela `uf` (`cod_uf` , `cod_uf` ), porém a tabela não possui o mesmo atributo, duplicado como chave primária. A referencia de um foreign key deve ser à chave primária da tabela para qual ele está apontando. Para corrigir crie duas constraints. assim:

CONSTRAINT `FK_reg_inst_002` FOREIGN KEY (`cod_uf_inst`) 
REFERENCES `uf`(`cod_uf`) ON DELETE NO ACTION ON UPDATE NO ACTION;
CONSTRAINT `FK_reg_inst_003` FOREIGN KEY (`cod_uf_resp`) 
REFERENCES `uf`(`cod_uf`) ON DELETE NO ACTION ON UPDATE NO ACTION;

6.4) A Terceira constraint (que agora é a quarta

CONSTRAINT `cod_pais`
    FOREIGN KEY (`cod_pais_inst` , `cod_pais_resp` )
    REFERENCES `reg_pais` (`cod_pais` , `cod_pais` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION[/codepossui os mesmos erros da segunda constraint. O correrto é assim:[code]CONSTRAINT `FK_reg_inst_004` FOREIGN KEY (`cod_pais_inst` ) REFERENCES `pais` (`cod_pais`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_reg_inst_005` FOREIGN KEY (`cod_pais_resp` ) REFERENCES `pais` (`cod_pais`) ON DELETE NO ACTION ON UPDATE NO ACTION;

[Dauro Freitas]

Obrigado Denis Courcy,

Eu esqueci de mudar o nome das tabelas, elas ficaram com o reg na frente.

Muito obrigado por retirar minhas dúvidas.