SQL only allow foreign keys where other column matches

Question

I have two tables,

CREATE TABLE ActivityCodes (
    ActivityCodeID INT NOT NULL PRIMARY KEY,
    LocationID INT NOT NULL
);

and

CREATE TABLE LocationSettings (
    LocationID INT NOT NULL PRIMARY KEY,
    DefaultFooActivityCodeID INT,
    FOREIGN KEY (DefaultFooActivityCodeID) REFERENCES ActivityCodes(ActivityCodeID)
);

with the foreign key relationship as indicated. Activity codes are valid only for the given LocationID and DefaultFooActivityCodeID in the LocationSettings table should be an ActivityCodeID where ActivityCodes.LocationID == LocationSettings.LocationID. How can I enforce that in SQL? Can it be done with constraints or foreign keys? Is it possible at all?

Edit: Just to add some clarification, this what valid data in these table should look like:

ActivityCodes

ActivityCodeID	LocationID
1	123
2	123
3	456
4	456

LocationSettings

LocationID	DefaultFooActivityCodeID
123	1
456	4

A location can have multiple activity codes. The default activity code for a location must be an activity code for that location. @Charlieface I tried using a composite foreign key as suggested in the answer you linked but I get an error saying LocationID on ActivityCodes is neither unique nor a primary key (I'm using MS SQL Server).

Does this answer your question? [Foreign key referencing a 2 columns primary key in SQL Server](https://stackoverflow.com/questions/3178709/foreign-key-referencing-a-2-columns-primary-key-in-sql-server) — Charlieface, Jan 01 '21 at 00:57
@Charlieface thanks for the link. Let me try to understand what that question and answer say since I'm not super familiar with SQL. Hopefully it will answer my question :) — Mirza Dobric, Jan 01 '21 at 01:21

score 0 · Answer 1 · answered Jan 01 '21 at 00:59

The DDL you provide, do not means what you have describe. The DDL describe this: The ActivityCodes are independent of LocationSettings. The LocationSettings instead depends on an ActivityCodes (one ActivityCodes has many LocationSettings)

The foreign key is well defined with this like FOREIGN KEY (DefaultFooActivityCodeID) REFERENCES ActivityCodes(ActivityCodeID). If you try to insert a LocationSettings without first insert an ActivityCodes it will fail due constraint violation.

score 0 · Answer 2 · answered Jan 01 '21 at 14:00

A foreign key reference does not have to be a primary key. This allows you to have two foreign key references to the same table, even if they are redundant:

CREATE TABLE ActivityCodes (
    ActivityCodeID INT NOT NULL PRIMARY KEY,
    LocationID INT NOT NULL,
    UNIQUE (LocationID, ActivityCodeID)
);

CREATE TABLE LocationSettings (
    LocationID INT NOT NULL PRIMARY KEY,
    DefaultFooActivityCodeID INT,
    FOREIGN KEY (DefaultFooActivityCodeID) REFERENCES ActivityCodes(ActivityCodeID),
    FOREIGN KEY (LocationID, DefaultFooActivityCodeID) REFERENCES ActivityCodes(LocationID, ActivityCodeID)
);

Although this expresses what you want, you will find that this is a bit tricky to maintain in practice. Setting the default requires the following steps:

Insert a location with a NULL default.
Insert a row into ActivityCodes with the default activity.
Update the default in LocationSettings.

SQL only allow foreign keys where other column matches

2 Answers2