Multiple Column Foreign Key Contraints

Foreign key referencing a 2 columns primary key in SQL Server

Of course it's possible to create a foreign key relationship to a compound (more than one column) primary key. You didn't show us the statement you're using to try and create that relationship - it should be something like:

ALTER TABLE dbo.Content
   ADD CONSTRAINT FK_Content_Libraries
   FOREIGN KEY(LibraryID, Application)
   REFERENCES dbo.Libraries(ID, Application)

Is that what you're using?? If (ID, Application) is indeed the primary key on dbo.Libraries, this statement should definitely work.

Luk: just to check - can you run this statement in your database and report back what the output is??

SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('Libraries', 'Content')

One foreign key with multiple columns VS multiple foreign keys with single column

My question: are SQL code 1 and SQL code 2 equal?

Not, they are not equivalent.

By the definition

Foreign Key Constraints

A foreign key constraint (also called a referential integrity
constraint) designates a column as the foreign key and establishes a
relationship between that foreign key and a specified primary or
unique key, called the referenced key. A composite foreign key
designates a combination of columns as the foreign key.

---

According to the above this declaration:

CONSTRAINT FK_E
      FOREIGN KEY (E1, E2, E3)
      REFERENCES F (E1, E2, E3),

assumes that there is either a primary key or unique constraint created on F table

CREATE TABLE F(
  .....
  .....
  CONSTRAINT my_pk PRIMARY KEY(E1, E2, E3)
)

---

while this declaration

 CONSTRAINT FK_E1
      FOREIGN KEY (E1)
      REFERENCES F (E1),

 CONSTRAINT FK_E2
      FOREIGN KEY (E2)
      REFERENCES F (E2),

 CONSTRAINT FK_E3
      FOREIGN KEY (E3)
      REFERENCES F (E3),

is in a need of existence of three constraints, either primary key or unique indexses/constraints:

CREATE TABLE F(
  .....
  .....
  CONSTRAINT my_pk1 PRIMARY KEY(E1),
  CONSTRAINT my_uq2 UNIQUE(E2),
  CONSTRAINT my_uq3 UNIQUE(E3)
)

---

Note 1- the table can only have one primary key, so only one constraint in your example could be the primary key, the rest 2 (or all 3) must be unique keys.

Note 2 - there is a slight semantic difference between the primary key constraint and the unique key constraint. The primary key values must be unique and must not contain null values while the unique key values can be NULL.

---

In the first case the table F can contain these values

E1  E2  E3
 1   1   1
 1   1   2
 2   2   1

and the child table can contain only these records:

E1  E2  E3
 1   1   1
 1   1   2
 2   2   1

but you cannot insert to the child table these combination of values because they don't exists in the parent table:

 E1  E2  E3
 1   2   1
 2   2   2

---

In the secondcase the table F can contain these values

E1  E2  E3
 1   1   1
 2   2   2
 3   3   3

but cannot contain these values, because each column must be unique:

E1  E2  E3
 1   1   1
 1   1   2
 2   1   3

while the child table can contain these records:

E1  E2  E3
 1   2   3
 3   1   2
 2   1   3

Multiple-column foreign key in MySQL?

Something like this ought to do it:

CREATE TABLE MyReferencingTable AS (
   [COLUMN DEFINITIONS]
   refcol1 INT NOT NULL,
   rofcol2 INT NOT NULL,
   CONSTRAINT fk_mrt_ot FOREIGN KEY (refcol1, refcol2)
                        REFERENCES OtherTable(col1, col2)
) ENGINE=InnoDB;

• MySQL requires foreign keys to be indexed, hence the index on the referencing columns
• Use of the constraint syntax enables you to name a constraint, making it easier to alter and drop at a later time if needed.
• InnoDB enforces foreign keys, MyISAM does not. (The syntax is parsed but ignored)

Adding foreign key on multiple columns

Tried it here and got the same error. This works though:

CREATE TABLE test2 (
  ID INT NOT NULL AUTO_INCREMENT,  
  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  PRIMARY KEY (ID),
  CONSTRAINT fk FOREIGN KEY (col1)
                REFERENCES test1(ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT,
  CONSTRAINT fk2 FOREIGN KEY (col2)
                REFERENCES test1(ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT

) ENGINE=InnoDB

Yes, I know - your script should work (even if it doesn't seem to make much sense). Yet, I guess this new version is better.

How to use two columns in a foreign key constraint

In Article Table I have two fields that are the primary key: id,sl. In the Subscription Table I have a foreign key 'idsl`

This design is broken - it is apparent that the composite primary key in Article(id, sl) has been mangled into a single compound foreign key in table Subscription. This isn't a good idea.

Instead, you will need to change the design of table Subscription to include separate columns for both id and sl, of the same type as the Article Table, and then create a composite foreign key consisting of both columns, referencing Article in the same order as the primary key, e.g:

CREATE TABLE Article
(
    id INT NOT NULL,
    sl VARCHAR(50) NOT NULL,
    -- Other Columns

    CONSTRAINT PK_Article PRIMARY KEY(id, sl) -- composite primary key
);

CREATE TABLE Subscription
(
    -- Other columns
    id INT NOT NULL, -- Same type as Article.id
    sl VARCHAR(50) NOT NULL, -- Same type as Article.sl

    CONSTRAINT FK_Subscription_Article FOREIGN KEY(id, sl) 
         REFERENCES Article(id, sl) -- Same order as Article PK
);

Edit

One thing to consider here is that by convention a column named table.id or table.tableid should be unique, and is the primary key for the table. However, since table Article requires an additional column sl in the primary key, it implies that id isn't unique.

Using the same column in multiple foreign keys

you can't change to lectureids 2 because none exists in the first place in the table

And the cascade works the other way

Further GROUPS os a reserved word in mysql, and should so be avoided.

Last i had to remove the second foreign key, as it already exists in groups and is so unnecessary, as the fpoeign key in groups in groups already chekcs if such a lectureid exists

EXAMPLE for the working of foreign keys

CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );

✓

CREATE TABLE `groups` (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  #FOREIGN KEY (lectureId) REFERENCES lectures (lectureId) 
  #  ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (lectureId,groupNo) REFERENCES `groups` (lectureId,groupNo)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TRIGGER GroupDelete BEFORE DELETE ON `groups`
FOR EACH ROW
  UPDATE studentListed SET studentListed.groupNo = NULL
    WHERE studentListed.lectureId = OLD.lectureId
    AND studentListed.groupNo = OLD.groupNo;

✓

INSERT INTO lectures
VALUES
(1, "lecture1");

✓

INSERT INTO `groups`
VALUES
(1, 1, "group1");

✓

INSERT INTO studentListed
VALUES
(1, 1, 1);

✓

UPDATE lectures SET lectureId = 2 WHERE lectureId = 1

✓

SELECT * FROM studentListed

studentId | lectureId | groupNo
--------: | --------: | ------:
        1 |         2 |       1

UPDATE lectures SET lectureId=2 WHERE lectureId=1; /* Offending line */

✓

✓

db<>fiddle here

yu can't build that in t your way you have to break up the primary key from Groups and reference only groups with it, like

CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );

✓

CREATE TABLE `groups` (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL PRIMARY KEY,
  title VARCHAR(10) NOT NULL,
  UNIQUE KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (groupNo) REFERENCES `groups` (groupNo)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TRIGGER GroupDelete BEFORE DELETE ON `groups`
FOR EACH ROW
  UPDATE studentListed SET studentListed.groupNo = NULL
    WHERE studentListed.lectureId = OLD.lectureId
    AND studentListed.groupNo = OLD.groupNo;

✓

INSERT INTO lectures
VALUES
(1, "lecture1");

✓

INSERT INTO `groups`
VALUES
(1, 1, "group1");

✓

INSERT INTO studentListed
VALUES
(1, 1, 1);

✓

UPDATE lectures SET lectureId = 2 WHERE lectureId = 1

✓

SELECT * FROM studentListed

studentId | lectureId | groupNo
--------: | --------: | ------:
        1 |         2 |       1

UPDATE lectures SET lectureId=2 WHERE lectureId=1; /* Offending line */

✓

✓

DELETE FROM lectures WHERE lectureID = 2

✓

SELECT * FROM studentListed

studentId | lectureId | groupNo
--------: | --------: | ------:

db<>fiddle here

this will need still more improvment as your concept egts more complicated

Usage of MySQL foreign key referencing multiple columns

1. Is it the same as creating three independent foreign keys?

No. Consider the following.

First off, it is not useful to think of it as (id,id,id), but rather (id1,id2,id3) in reality. Because a tuple of (id,id,id) would have no value over just a single column index on id. As such you will see the schema below that depicts that.

create schema FKtest001;
use FKtest001;

create table user
(   id int auto_increment primary key,
    fullname varchar(100) not null,
    id1 int not null,
    id2 int not null,
    id3 int not null,
    index `idkUserTuple` (id1,id2,id3)
);

create table device
(   id int auto_increment primary key,
    something varchar(100) not null,
    user_created_id int not null,
    user_updated_id int not null,
    user_deleted_id int not null,
    foreign key `fk_device_user` (`user_created_id` , `user_updated_id` , `user_deleted_id`)
       REFERENCES `user` (`id1` , `id2` , `id3`)

);
show create table device;
CREATE TABLE `device` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `something` varchar(100) NOT NULL,
   `user_created_id` int(11) NOT NULL,
   `user_updated_id` int(11) NOT NULL,
   `user_deleted_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_device_user` (`user_created_id`,`user_updated_id`,`user_deleted_id`),
   CONSTRAINT `device_ibfk_1` FOREIGN KEY (`user_created_id`, `user_updated_id`, `user_deleted_id`) REFERENCES `user` (`id1`, `id2`, `id3`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
show indexes from device; -- shows 2 indexes (a PK, and composite BTREE)
-- FOCUS heavily on the `Seq_in_index` column for the above

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

drop table device;
drop table user;

create table user
(   id int auto_increment primary key,
    fullname varchar(100) not null,
    id1 int not null,
    id2 int not null,
    id3 int not null,
    index `idkUser1` (id1),
    index `idkUser2` (id2),
    index `idkUser3` (id3)
);

create table device
(   id int auto_increment primary key,
    something varchar(100) not null,
    user_created_id int not null,
    user_updated_id int not null,
    user_deleted_id int not null,
    foreign key `fk_device_user1` (`user_created_id`)
       REFERENCES `user` (`id1`),
    foreign key `fk_device_user2` (`user_updated_id`)
       REFERENCES `user` (`id2`),
    foreign key `fk_device_user3` (`user_deleted_id`)
       REFERENCES `user` (`id3`)
);
show create table device;
CREATE TABLE `device` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `something` varchar(100) NOT NULL,
   `user_created_id` int(11) NOT NULL,
   `user_updated_id` int(11) NOT NULL,
   `user_deleted_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_device_user1` (`user_created_id`),
   KEY `fk_device_user2` (`user_updated_id`),
   KEY `fk_device_user3` (`user_deleted_id`),
   CONSTRAINT `device_ibfk_1` FOREIGN KEY (`user_created_id`) REFERENCES `user` (`id1`),
   CONSTRAINT `device_ibfk_2` FOREIGN KEY (`user_updated_id`) REFERENCES `user` (`id2`),
   CONSTRAINT `device_ibfk_3` FOREIGN KEY (`user_deleted_id`) REFERENCES `user` (`id3`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 show indexes from device; -- shows 4 indexes (a PK, and 3 indiv FK indexes)
-- FOCUS heavily on the `Seq_in_index` column for the above

There are 2 sections there. The show indexes from device will show the difference of, in the top part, 2 indexes maintained. In the bottom part, 4 indexes maintained. If for some reason the index tuple in the top part is useful for the system, then that tuple approach is certainly the way to go.

The reason is the following. The tuple exists as a group. Think of it as an instance of a set that has meaning as a group. Compare that to the mere existence of the individual parts, and there is a difference. It is not that the users exist, it is that there is a user row that has that tuple as an existence.

2. Are there any pros / cons of using one or another?

The pros were described above in the last paragraph: existence as an actual grouping in the user table as a tuple.

They are apple and oranges and used for different purposes.

3. What is the exact use-case for this? (main question)

A use case would be something that requires the existence of the tuple as a group, as opposed to the existence of the individual items. It is used for what is called compositing. Compositing FK's in particular. See this answer of mine Here as one case.

In short, it is when you want to enforce special hard to think of solutions that require Referential Integrity (RI) at a composited level (groupings) of other entities. Many people think it can't be done so they first think TRIGGER enforcement or front-end Enforcement. Fortunately those use cases can be achieved via the FK Composites thus leaving RI at the db level where it should be (and never at the front-end).

Addendum

Request from OP for a better real life example than the link above.

Consider the following schema:

CREATE SCHEMA testRealLifeTuple;
USE testRealLifeTuple;

CREATE TABLE contacts
(   id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(100) NOT NULL
    -- etc
);

CREATE TABLE tupleHolder
(   -- a tuple representing a necessary Three-some validation
    -- and vetting to get financing
    --
    -- If you can't vett these 3, you can't have my supercomputer financed
    --
    id INT AUTO_INCREMENT PRIMARY KEY,
    CEO INT NOT NULL,   -- Chief Executive Officer
    CFO INT NOT NULL,   -- Chief Financial Officer
    CIO INT NOT NULL,   -- Chief Geek
    creditWorthiness INT NOT NULL, -- 1 to 100. 100 is best

    -- the unique index is necessary for the device FK to succeed
    UNIQUE INDEX `idk_ContactTuple` (CEO,CFO,CIO), -- No duplicates ever. Good for re-use

    FOREIGN KEY `fk_th_ceo` (`CEO`) REFERENCES `contacts` (`id`),
    FOREIGN KEY `fk_th_cfo` (`CFO`) REFERENCES `contacts` (`id`),
    FOREIGN KEY `fk_th_cio` (`CIO`) REFERENCES `contacts` (`id`)
);

CREATE TABLE device
(   -- An Expensive Device, typically our Supercomputer that requires Financing.
    -- This device is so wildly expense we want to limit data changes
    --
    -- Note that the GRANTS (privileges) on this table are restricted.
    --
    id INT AUTO_INCREMENT PRIMARY KEY,
    something VARCHAR(100) NOT NULL,
    CEO INT NOT NULL,   -- Chief Executive Officer
    CFO INT NOT NULL,   -- Chief Financial Officer
    CIO INT NOT NULL,   -- Chief Geek
    FOREIGN KEY `fk_device_2_tuple` (`CEO` , `CFO` , `CIO`)
        REFERENCES `tupleHolder` (`CEO` , `CFO` , `CIO`)
    --
    -- Note that the GRANTS (privileges) on this table are restricted.
    --
);

DROP SCHEMA testRealLifeTuple;

The highlights of this schema come down to the UNIQUE KEY in tupleHolder table, the FK in device, the GRANT restriction (grants not shown), and the fact that the device is shielded from tomfoolery edits in the tupleHolder because of, as mentioned:

• GRANTS
• That the FK must be honored, so the tupleHolder can't be messed with

If the tupleHolder was messed with (the 3 contacts ids), then the FK would be violated.

Said another way, it is NO WAY the same as the device having an FK based on a single column in device, call it [device.badIdea INT], that would FK back to tupleHolder.id.

Also, as mentioned earlier, this differs from merely having the contacts exist. Rather, it matters that the composition of contacts exists, it is a tuple. And in our case the tuple has been vetted, and has a credit worthiness rating, and the id's in that tuple can't be messed with, after a device is bought, unless sufficient GRANTS allow it. And even then, the FK is in place.

It may take 15 minutes for that to sink in, but there is a Huge difference.

I hope this helps.

---

Related Topics