Foreign key for different tables (MySql)

Question

Foreign key for different tables (MySql)

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

At the time of executing the error query stating that it is not possible to create the foreign keys, I looked in the documentation and did not understand whether 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:

Can not add foreign key constraint

mysql sql foreign-key

asked by anonymous 21.08.2018 / 05:24

2 answers

1

The variable you were referring to was wrong. It would be 'cod_autor'.

 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 it will not be:

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

21.08.2018 / 09:53

Capture using Xpath Segmentation Fault with malloc

score 2 · Accepted Answer

You have several errors:

Need a PK field in livro
Missing size in razao text,
You are making wrong foreign key references ("foreign key").

Correcting

1 - I added one more field to be primary key:

create table livro(
    livro_id int,

2 - Column razao :

razao text(N),

N = Characters to display in the 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

When creating the field razao :

Missing by size. Example: text(30) .

In the first foreign key:

In foreign key(cd_autor) you are saying that the cd_autor column of the livro table will be the foreign key.

And in references autor(cod_autor) you are referencing the autor table and the column you will reference will be the cod_autor (which the primary key)

In the second statement:

In foreign key(cd_editora) you are saying that the cd_editora column of the livro table will be the foreign key.

And in references autor(cod_editora) you are referencing the editora table and the column you will reference will be the cod_editora (which the primary key)

In addition, you can not set ON UPDATE SET NULL ON DELETE SET NULL if you require that the cd_autor column of the livro table is not NULL : cd_autor integer NOT NULL,

So either you take these conditions, or you do not set the column to NOT NULL .

Important

The foreign key requires a reference to a primary and / or single key ( PRIMARY KEY and / or UNIQUE ).

Example

See the example of your script running in SQLFiddle .