Mysql create table with two foreign keys referencing primary key

Question

I have two mysql tables. The first one is created using the following code:

create table project(
project_id int not null auto_increment,
project_name varchar(30)
primary key(project_id));

The second table:

create table matches(
match_id int not null auto_increment,
match_name varchar(30),
project_id int(4) foreign key (project_id) references projects(project_id));

Both these commands works fine. I want to add the project_name column from the first table to the second table. I tried using

alter table projects drop primary key, add primary key(project_id, project_name);

and then

alter table matches add column project_name varchar(30), add foreign key (project_name) references projects(project_name);

But got the following error:

ERROR 1005 (HY000): Can't create table 'matches.#sql-55e_311' (errno: 150)

How do i include both the columns from the first table into the second table. The current structure of my second table is as follows:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| match_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| match_name | varchar(30) | YES  |     | NULL    |                |
| project_id | int(4)      | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

I want to add the project_name as the fourth column in my second table.

Try dropping a foreign key from the second table and add a 2 column foreign key(project_id, project_name) so that it matches a 2 column primary key. — Sergey Benzenko, Jan 12 '18 at 07:18
@SergeyBenzenko Thank you. I recreated the second table with two primary keys and referencing foreign keys. Can accept this as an answer. — popeye, Jan 12 '18 at 07:30
There are problems with doing what you want with project_id nullable in projects. Also note that (project_id, project_name) is *not* two PKs, it is one. See my (edited) answer. — philipxy, Jan 12 '18 at 08:06

score 1 · Answer 1 · answered Jan 12 '18 at 07:28

1

To use a compound Primary Key as Foreign Key, you'll have to add the same number of columns (that compose the PK) with same datatypes to the child table and then use the combination of these columns in the FOREIGN KEY definition.

see related post here https://stackoverflow.com/a/10566463/4904726

so try this way

alter table matches add foreign key (project_id, project_name) references projects(project_id, project_name);

answered Jan 12 '18 at 07:28

vadzim dvorak

939
6
24

1

There is a problem with project_id nullable in projects, both for PK & FK. See my answer. – philipxy Jan 12 '18 at 08:07

philipxy · Answer 2 · 2018-01-12T08:05:33.677

Do you understand what a FK constraint says? It says a list of values for some columns in a table must appear as a list of values for some columns forming a (declared) PK or UNIQUE NOT NULL in the referenced table. So what you are writing doesn't make sense. If you wanted a PK (project_id, project_name) then that should also be the form of your FK.

But (project_id, project_name) is not two 1-column PKs, it is one 2-column PK, and it is probably not what you want since presumably in projects it isn't just pairs that are unique it is each column. Presumably you want two 1-column PKs and two one-column FKs, one referencing each PK.

If projects project_id was NOT NULL you could write:

alter table projects add primary key(project_name);
alter table matches add column project_name varchar(30),
    add foreign key (project_name) references projects(project_name);

But if project_name can be NULL in projects then you cannot make it a PK and you cannot sensibly have a FK to it. You can make it UNIQUE. Unfortunately MySQL lets you write such a FK declaration to a non-NULL UNIQUE column while it also tells you not to do it:

The handling of foreign key references to non-unique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only keys that are both UNIQUE (or PRIMARY) and NOT NULL.

So if you want projects project_name to be nullable then should declare it UNIQUE but you should enforce the logical matches project_name match with a trigger.

Mysql create table with two foreign keys referencing primary key

2 Answers2