Using Composite Primary Key As Foreign Key

Question

I've always thought that if I were going to use a foreign key that referenced a composite primary key, I'd need to include all the columns of the composite primary key in both tables.

I was confused when I inadvertently didn't do this and received no errors. In the example below, RoomId ISN'T unique, and yet it's allowed to be used as a foreign key by itself.

CREATE TABLE Buildings ( BuildingName VARCHAR(4) PRIMARY KEY, CampusId TINYINT, StreetAddress VARCHAR(50), City VARCHAR(30), State CHAR(2), Zip CHAR(5) ); CREATE TABLE Rooms ( RoomId VARCHAR(5), BuildingName VARCHAR(20) NOT NULL, RoomType VARCHAR(15), Capacity INT, Notes VARCHAR(100), CONSTRAINT FK_Buildings_Rooms FOREIGN KEY (BuildingName) REFERENCES Buildings(BuildingName), PRIMARY KEY (RoomId, BuildingName) ); CREATE TABLE Instructors( EmployeeId INT AUTO_INCREMENT PRIMARY KEY, OfficeId VARCHAR(5), CONSTRAINT Fk_Instructors_Rooms FOREIGN KEY (OfficeId) REFERENCES Rooms(RoomId));

If I switch the order in the composite primary key to (BuildingName, RoomId), then the foreign key declaration produces the expected error: Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'Fk_Instructors_Rooms' in the referenced table 'rooms'

Gordon Linoff · Accepted Answer · 2019-10-07 20:10:25Z

MySQL has extended the traditional definition of foreign keys. In most databases, they are limited to unique or primary keys. As with some other "extensions", the documentation explicitly warns against referring to a non-unique key:

However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique 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 UNIQUE (including PRIMARY) and NOT NULL keys.

Of course, not including all the components of a composite key means that you are using a non-unique key.

Your issue with composite primary keys is an interesting one. Personally, I would chalk it up as yet another reason to use auto-incremented primary keys. Single columns are less prone to error in foreign key declarations.

Uueerdo · Accepted Answer · 2019-10-07 19:40:26Z

0

Foreign keys do not need to reference unique or primary key fields. They can reference the first field(s) of any index (primary, unique, or plain).

answered Oct 7, 2019 at 19:40

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

1 Comment

Uueerdo Over a year ago

I never really looked at it that way, it's not a feature I've ever felt would be useful to me so I have never used it, but I suppose so. My main point was that if you have an index on (a, b, c) you can have foreign keys referencing the table with (a), (a, b), or (a, b, c); but not (b), (c), (b, a), etc.... which is why you had an issue when you swapped them around in the referenced index.

Collectives™ on Stack Overflow

Using Composite Primary Key As Foreign Key

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related