Foreign key for different tables (Mysql)

Question

When executing the error query stating that it is not possible to create the Foreign Keys, I looked in the documentation and did not understand right if this code is possible or not.

create table autor(
    cod_autor integer,
    nome varchar(50) NOT NULL,
    nascimento date NOT NULL,
    primary key(cod_autor)
);

create table editora(
    cod_editora int,
    razao text,
    endereco varchar(50),
    cnpj int NOT NULL UNIQUE,
    cidade varchar(40),
    primary key(cod_editora)
);


create table livro(
    titulo varchar (100),
    cd_autor integer NOT NULL,
    cd_editora integer,
    valor float NOT NULL,
    publicacao DATE not null,
    volume INTEGER NOT NULL,
    primary key (titulo,cd_autor),
    foreign key(cd_autor) references autor(cd_autor) ON UPDATE SET NULL  ON DELETE SET NULL,
    foreign key (cd_editora) references autor(cd_autor) ON UPDATE SET NULL ON DELETE SET NULL
);

Error :

Cannot add Foreign key Constraint

Answer 1

You have several mistakes:

• Needs a PK field in livro
• Is missing the size in razao text,
• Are you doing wrong references of foreign keys ("Foreign key").

---

Correcting

1 - I added another field to be primary key:

create table livro(
    livro_id int,

2 - Column razao:

razao text(N),

N = Characters to be displayed in column.

3 - Foreign keys:

foreign key(cd_autor) references autor(cod_autor),
foreign key (cd_editora) references editora(cod_editora) ON UPDATE SET NULL ON DELETE SET NULL

---

Detailing

In creating the field razao:

Missing by size. Example: text(30).

On the first foreign key:

In foreign key(cd_autor) you’re saying the column cd_autor table livro will be the foreign key.

And in references autor(cod_autor) you are making reference to table autor and the column that will reference, will be the cod_autor (that the primary key)

In the second statement:

In foreign key(cd_editora) you’re saying the column cd_editora table livro will be the foreign key.

And in references autor(cod_editora) you are making reference to table editora and the column that will reference, will be the cod_editora (that the primary key)

Besides, you can’t set ON UPDATE SET NULL ON DELETE SET NULL if you require the column cd_autor table livro not be NULL: cd_autor integer NOT NULL,

So either you take out these conditions, or you don’t define the column as NOT NULL.

---

Important

Foreign key requires reference to a primary and/or unique key (PRIMARY KEY and/or UNIQUE).

Example

---

See the example of your script working on Sqlfiddle.

Answer 2

The variable you referenced was wrong. It would be cod_author'.

 foreign key(cd_autor) references autor(cod_autor) ON UPDATE SET NULL  ON DELETE SET NULL,
 foreign key (cd_editora) references autor(cod_autor) ON UPDATE SET NULL ON DELETE SET NULL

As for the second Foreign key will not be:

foreign key (cd_editora) references editora(cod_editora) ON UPDATE SET NULL ON DELETE SET NULL