SQL table foreign key that is part of a composite primary key

Question

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

Project table:

Composite Primary Keys: UserId, ProjectId (they are not unique in themselves)

Equipment table:

Composite Primary Keys: UserId, ProjectId, EquipmentId (they are not unique in themselves)

Is it now possible to set ProjectId in the equipment table as a foreign key from the project table? When I try, do I get a message stating that the column in the project table does not match the existing primary key or unique constraint?

Answer 1

Not.

When you create a foreign key, the key that you "specify" in another table must be a UNIQUE or PRIMARY KEY constraint. You cannot set a foreign key that points to a column that allows duplicate values. It would be very difficult to imagine how the data should "act" if you update one of the duplicate values ​​in another table (for example).

To do what you want, you must set up a Projects table in which ProjectID is UNIQUE or PRIMARY KEY, and then specify foreign keys in both tables in that table.

In a conditional expression, 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. This key may consist of one or more columns, but the key itself is still the only one. This is an important difference when using a primary key to optimize your search.

Answer 2

He doesn’t know if it’s good design practice, but he probably has a composite foreign key for one table that is part of a composite primary key for another table.

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

Now we can have a table in which test2 has the primary key (P, Q, R), where in (P, Q) the links are test2 (A, B) of test2.

I ran the following script in the MySql database and it works 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 case, the database expects the combination (A, B) to be unique, and it is the primary key in table test1.

But if you try to do something like the following, the script will fail. The database will not allow the creation of 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 case, the database expects column A to be unique individually, and the same goes for column B. It does not matter if the combination (A, B) is unique.

Answer 3

@Larry Lustig A foreign key may be part of the primary key in another table.

Source: Dependent Link

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