Foreign key for different tables (Mysql)

Question

Foreign key for different tables (Mysql)

Asked 6 years, 3 months ago

Viewed 1,251 times

0

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

2 answers

2

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.

As for the reference, it was a wrong edition of me in the question before publishing, the error was exactly in ON UPDATE and ON DELETE. The table livro has PK: primary key (titulo,cd_autor),

– pic

2018/08/21 at 15:45
@pic Also there. If you don’t understand something in the answer, you can ask! ;)

– rbz

2018/08/21 at 15:48

Browser other questions tagged mysql sql foreign-key

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

by White • **384** points · Answer 1 · 2018-08-21T07:53:54+00:00

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