Mysql – Double FOREIGN KEY, i.e. how to create a table with two different FOREIGN KEYs that reference a single PRIMARY KEY in another table – DDL

foreign keyMySQL

I have an issue with my SQL syntax when creating a table with a set of Foreign Key (Orig_Dept_ID & New_Dept_ID) that references Dept_ID in the Department Table.

Below is my sql syntax:

CREATE TABLE DEPT_TRANSIT
    (Orig_Dept_ID int,
    New_Dept_ID int,
    CONSTRAINT department_fk foreign key (Orig_Dept_ID, New_Dept_ID)
             REFERENCES Department(Dept_ID)
    );

Could someone please advise on how I should go about doing this?

Thanks alot!

Best Answer

There are two possibilities here:

  • 1) is that it is a case of 2 fields in the same table being FOREIGN KEYs pointing to the same field in the parent table.

  • 2) is that this is an example of an Associative Entity. These are also called joining, linking or many-to-many tables.

1. Double FOREIGN KEY.

This is quite simple to resolve.

You have two tables - department and dept_transit (see fiddle):

CREATE TABLE department
(
  department_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT department_pk PRIMARY KEY (department_id)
);

CREATE TABLE dept_transit
(
  dt_original_dept_id INTEGER NOT NULL,
  dt_new_dept_id      INTEGER NOT NULL,
  CONSTRAINT dt_original_dept_id_fk FOREIGN KEY (dt_original_dept_id) REFERENCES department (department_id),
  CONSTRAINT dt_new_dept_id_fk      FOREIGN KEY (dt_new_dept_id)      REFERENCES department (department_id)
  -- optional here CONSTRAINT dept_transit_pk PRIMARY KEY (dt_original_dept_id, dt_new_dept_id)
);

A few things to notice:

  • my solution uses long_variable_names! Many years of experience have taught me that you are far better off making your variable names (table_names, field_names and PKs, FKs &c.) as long and as meaningful as necessary. This makes life much easier for debugging and more than outweighs the trouble of the extra typing! Also, note the singular name for department! YMMV :-)

  • I always try to keep fieldnames unique within a schema (e.g. new_dept_id, dt_new_dept_id) - this helps greatly with logic when joining tables. I make exceptions for NATURAL KEYs, but not surrogate ones as is the case here.

  • My own personal preference is for SQL keywords to be in upper-case and for the variables (tables, fields &c.) to be in lower_snake-case. This allows the eye to readily pick out different elements when debugging &c. If you look up SQL coding standards on the web, you will find lots of debate about this (i.e. here). The main thing is to pick a standard (for yourself, company, whatever) and STICK TO IT.

  • Not entirely sure of requirements, but I think that (for MySQL) a TRIGGER will be required for any requirement that original_dept_id and new_dept_id be different (which makes sense - I think). Unfortunately, MySQL still (Amazingly) doesn't allow for CHECK CONSTRAINTs. See the MUCH nicer solution possible with PostgreSQL here. A good reason to switch to the far superior PostgreSQL if you're not too invested in MySQL! :-)

2. Joining Table.

If this is the case, I would do something like the following (fiddle here):

CREATE TABLE original_dept
(
  original_dept_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT original_dept_pk PRIMARY KEY (original_dept_id)
);


CREATE TABLE new_dept
(
  new_dept_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT new_dept_pk PRIMARY KEY (new_dept_id)
);



CREATE TABLE dept_transit
(
  dt_original_dept_id INTEGER NOT NULL,
  dt_new_dept_id      INTEGER NOT NULL,
  CONSTRAINT dt_original_dept_id_fk FOREIGN KEY (dt_original_dept_id) REFERENCES original_dept (original_dept_id),
  CONSTRAINT dt_new_dept_id_fk      FOREIGN KEY (dt_new_dept_id)      REFERENCES new_dept (new_dept_id),
  CONSTRAINT dept_transit_pk PRIMARY KEY (dt_original_dept_id, dt_new_dept_id)
);
  • The PRIMARY KEY of the joining table is a natural one, make up of the PKs of both of the entities that comprise it.
Related Question