SQL Table Foreign Key that is part of a Composite

问题:

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?

回答1:

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 the singular. This is an important difference when using the primary key to optimize searches.

回答2:

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.

回答3:

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