10

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?

Improve this question
6
  • How have you defined the composite primary key? Have you added a unique constraint on each column? Commented Oct 1, 2012 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. Commented Oct 1, 2012 at 19:03
  • I asked how you defined the constraints. I didn't say you should define them that way. Commented Oct 1, 2012 at 19:04
  • Selected them all and click on set primary key. They all have a yellow key symbol next to the column names. Commented Oct 1, 2012 at 19:05
  • 1
    Related: stackoverflow.com/questions/6651667/… Commented Oct 1, 2012 at 19:23

3 Answers 3

Reset to default
14

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

"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?
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?
-1

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.

Improve this answer

2 Comments

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 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.
-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)

football Competition - database

Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.