SQL Table Foreign Key that is part of a Composite Primary Key

Question

Is it possible to have a table's foreign key be part of another table's composite primary key? For example, if I have two tables, one contains information on all active projects of different users and another containing information on what equipment is being used by the projects:

Project Table:

Composite Primary Keys: UserId, ProjectId (neither are unique by themselves)

Equipment Table:

Composite Primary Keys: UserId, ProjectId, EquipmentId (neither are unique by themselves)

Now is it possible to set the ProjectId in the equipment table to be a foreign key from the project table? When I try, I get an error saying that the column in Project Table do not match an existing primary key or unique constraint?

How have you defined the composite primary key? Have you added a unique constraint on _each_ column? — Oded, Oct 01 '12 at 19:00
But I don't want each column to be unique. I want a combination of the two or three columns to be unique. — Tony, Oct 01 '12 at 19:03
I asked how you defined the constraints. I didn't say you should define them that way. — Oded, Oct 01 '12 at 19:04
Selected them all and click on set primary key. They all have a yellow key symbol next to the column names. — Tony, Oct 01 '12 at 19:05
Related: http://stackoverflow.com/questions/6651667/creating-a-composite-foreign-key-in-sql-server-2008 — Oded, Oct 01 '12 at 19:23
A FK must reference a unique column set (PK or UNIQUE). Whatever you want to express--you don't say what it is you want the associated DB restriction to be--it isn't a FK. — philipxy, Feb 19 '20 at 12:21

Larry Lustig · Accepted Answer · 2020-08-26T16:04:15.603

15

No.

When you create a foreign key, the key that you "point to" in the other table must be a UNIQUE or PRIMARY KEY constraint. You cannot establish a foreign key that points to a column that allow duplicate values. It would be very hard to imagine how the data should "act" if you update one of the duplicate values in the other table (for instance).

To do what you want you must establish a Projects table in which ProjectID is UNIQUE or a PRIMARY KEY and then point foreign keys in both the other tables to that table.

Parenthetically, you use the term "Primary Keys" to describe the columns in each table that make up the primary key. In fact, each table can have one and only one primary key. That key can be composed of one or more columns, but the key itself is still referred to in the singular. This is an important difference when using the primary key to optimize searches.

edited Aug 26 '20 at 16:04

answered Oct 01 '12 at 19:06

Larry Lustig

49,320
14
110
160

"You cannot establish a foreign key that points to a column that allow duplicate values. It would be very hard to imagine how the data should "act" if you update one of the duplicate values in the other table (for instance)." Could you expand on that? – pellucidcoder Aug 25 '20 at 04:27
1

If you have an invoice header table with two Invoice #10 records, and an invoice lines table with eleven Invoice #10 records, which of the lines in the second table go with which of the records in the first table? If you change the second header record from 10 to 15, which of the lines in the detail table should have their values changed to match? If you delete one of the invoice #10 records in the header table, which of the lines in the detail table are no longer valid? – Larry Lustig Aug 26 '20 at 16:06

Mav55 · Answer 2 · 2017-02-22T15:54:56.323

It do not know if that's a good design practice but for sure it is possible to have a composite foreign key of one table that is the part of the composite primary key of other table.

Say we have a table test1 having a composite primary key (A, B)

Now we can have a table say test2 having primary key (P, Q, R) where in (P,Q) of test2 referencing (A,B) of test2.

I ran the following script in the MySql database and it works just fine.

CREATE TABLE `test1` (
`A` INT NOT NULL,
`B` VARCHAR(2) NOT NULL,
`C` DATETIME NULL,
`D` VARCHAR(45) NULL,
PRIMARY KEY (`A`, `B`));

CREATE TABLE `test2` (
`P` INT NOT NULL,
`Q` VARCHAR(2) NOT NULL,
`R` INT NOT NULL,
`S` DATETIME NULL,
`T` VARCHAR(8) NULL,
PRIMARY KEY (`P`, `Q`, `R`),
INDEX `PQ_idx` (`P`,`Q` ASC),
CONSTRAINT `PQ`
  FOREIGN KEY (`P`, `Q`)
  REFERENCES `test1` (`A`,`B`)
  ON DELETE CASCADE
  ON UPDATE CASCADE);

In the above mentioned case, the database is expecting the combination of (A,B) to be unique and it is, being a primary key in test1 table.

But if you try to do something like following, the script would fail. The database would not let you create the test2 table.

CREATE TABLE `test2` (
`P` INT NOT NULL,
`Q` VARCHAR(2) NULL,
`R` DATETIME NULL,
`S` VARCHAR(8) NULL,
`T` VARCHAR(45) NULL,
  INDEX `P_idx` (`P` ASC),
  INDEX `Q_idx` (`Q` ASC),
  CONSTRAINT `P`
    FOREIGN KEY (`P`)
    REFERENCES `test1` (`A`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Q`
    FOREIGN KEY (`Q`)
    REFERENCES `test1` (`B`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

In the above mentioned case database would expect the column A to be unique individually and the same follows for column B. It does not matter if combination of (A,B) is unique.

What the author wants is reverse: `test1` must have a foreign key `(A, B)` referencing `(P, Q)` of `test2`. That is, as long as there is at least one `(P, Q, R)` row where `(P=A, Q=B)` in `test2`, it should be valid to have an `(A, B)` row in `test1`. That's what he wants. — izogfif, Nov 19 '20 at 13:14
@izogfif my understanding is totally correct. Not sure if yours is. you need to understand the context of my answer. if you only understand parroting, that's not my problem. — Mav55, Nov 19 '20 at 17:37

KamilJ · Answer 3 · 2015-08-18T18:55:13.327

-2

@Larry Lustig The foreign key can be part of primary key in the other table.

source: Dependent relationship

Check relationship between tables: Zdarzenie(Event) and TypZdarzenia (type of event)

edited Aug 18 '15 at 18:55

answered Aug 18 '15 at 10:48

KamilJ

249
3
5
14

Don't those two tables show diferent Ids, IdZ vs IdFZ? I imagine there are columns they are now showing but are understood to be there and one is the FK. – Raúl Moreno May 13 '22 at 09:48

SQL Table Foreign Key that is part of a Composite Primary Key

3 Answers3

Linked