Problem connecting tables using a foreign key

Asked

Viewed 33 times

0

Good afternoon, I have a little problem making the link between the tables, I need to connect the athlete table and make the link with the document sending organ table. The following error appears when I try to create the athlete table:

"Error code 1215. Cannot add Foreign key Constraint."

I already created the modality table, suit and orgao_ex. If anyone can tell me what mistake I appreciate.

Down with the darlings:

CREATE TABLE modalidade (
id INT NOT NULL PRIMARY KEY auto_increment,
nome VARCHAR(50) NOT NULL,
CONSTRAINT uk_id UNIQUE (id)
) ENGINE=INNODB;

CREATE TABLE naipe (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
nome VARCHAR(50) NOT NULL,
CONSTRAINT uk_id UNIQUE (id)
) ENGINE=INNODB;

CREATE TABLE competicao (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
modalidade INT NOT NULL,
naipe INT NOT NULL,
inicio DATETIME,
fim DATETIME,
CONSTRAINT uk_id UNIQUE (id),
CONSTRAINT fk_modalidade FOREIGN KEY (modalidade)
REFERENCES modalidade,
CONSTRAINT fk_naipe FOREIGN KEY (naipe)
REFERENCES naipe
) ENGINE=INNODB;

CREATE TABLE relac_atleta (
competicao INT NOT NULL PRIMARY KEY,
atleta INT NOT NULL,
CONSTRAINT uk_competicao UNIQUE (compteticao),
CONSTRAINT uk_atleta UNIQUE (atleta),
CONSTRAINT fk_competicao FOREIGN KEY (competicao)
REFERENCES competicao,
constraint fk_atleta foreign key (atleta)
references atleta
) ENGINE=INNODB;

CREATE TABLE orgao_exp (
id INT NOT NULL PRIMARY KEY,
descricao VARCHAR(200) NOT NULL,
sigla VARCHAR(15),
CONSTRAINT _uk_id UNIQUE (id)
) ENGINE=INNODB;

CREATE TABLE atleta (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
nome VARCHAR(60) NOT NULL,
rg VARCHAR(20) NOT NULL,
orgao INT NOT NULL,
uf_rg CHAR(2) NOT NULL,
nascimento DATE NOT NULL,
CONSTRAINT fk_orgao FOREIGN KEY (orgao)
REFERENCES orgao_exp (orgao)
) ENGINE=INNODB;
mysql database modeling

  • In which table this FK is and in which table it will be linked?

  • 1

    your orgao_exp table does not have the orgao field....

  • Lucas is so right

2 answers

0

I suggest using software to model the database and generate automatic SQL. I use freeware Dbdesigner4. Split into two steps, the creation of the table and later the modification with inclusion of keys.

  • Good afternoon first thank you for your time and willingness to answer my question. So friend, this table is an activity of the technical course where I study, has to be done manually modeling using Mysql, but thanks for the tip I will use in creating other databases.

0

In your table orgao_exp there is no field orgao, which you are referencing in the table ATHLETE.

CREATE TABLE orgao_exp (
id INT NOT NULL PRIMARY KEY,
--orgao
descricao VARCHAR(200) NOT NULL,
sigla VARCHAR(15),
CONSTRAINT _uk_id UNIQUE (id)
) ENGINE=INNODB;

Browser other questions tagged mysql database modeling

You are not signed in. Login or sign up in order to post.