Referencing a Composite Primary Key

Referencing a composite primary key

Foreign keys have to match the primary/unique key they reference column for column. Since the primary key of BOOKING_REFERENCE is (REFERENCE_ID, CUST_ID), that means that the foreign key from BR_FLIGHT to BOOKING_REFERENCE must consist of 2 columns also. That means you need to add CUST_ID to the BR_FLIGHT table - either that or your BOOKING_REFERENCE primary key is wrong and should just be (REFERENCE_ID).

That said, it doesn't make sense to have foreign keys defined in both directions as you do. The "child" table should reference the "parent" and not vice versa.

SQL: Foreign key references a composite primary key

If you have a compound PK made up from three columns, then any child table that wants to establish a foreign key relationship must ALSO have all those 3 columns and use all 3 columns to establish the FK relationship.

FK-PK relationship is an all or nothing proposal - you cannot reference only parts of a primary key - either you reference all columns - or you don't reference.

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,

    Person varchar(50) NOT NULL PRIMARY KEY,

    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    CONSTRAINT PFK 
        FOREIGN KEY (First_Name, Name, Address) 
        REFERENCES Person (First_Name, Name, Address)
);

How to reference a composite primary key in SQL

We declare an SQL FK (FOREIGN KEY) constraint to say that a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that forms an SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. Declare it whenever it isn't already implied by other declarations. It must reference the column list in a declared SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. So you must declare that in the referenced table, even if that's already implied by NOT NULLs and a smaller contained PK or UNIQUE NOT NULL.

So note that an SQL PK is not necessarily a PK in the relational sense of being unique but not containing a smaller unique column set, ie being a superkey not containing a smaller superkey, ie being a minimal/irreducible superkey, ie being a CK (candidate key).

Here, you might need to replace the buildingno & roomno FKs by one, (buildingno, roomno) to Room:

CONSTRAINT SESSION_FK12
    FOREIGN KEY(BUILDINGNO,ROOMNO) REFERENCES ROOM(BUILDINGNO,ROOMNO)

That might be appropriate for the meanings of your tables--which in fact you don't give, so we can't know, we can only guess. Eg if buildingno could also be declared PK or UNIQUE NOT NULL in Room, which when roomno IS NOT NULL is actually consistent with and implies (buildingno, roomno) could be declared PK or UNIQUE NOT NULL, maybe your FK is right but your Room declarations are inadequate.

When a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that is called an IND (inclusion dependency) constraint. There's no way to declare a non-FK IND in SQL; we must enforce by triggers. That also might be what you need for your design.

You could keep the FK from buildingno to Building, but it's implied by the FK I suggest and the FK in buildingno on Room referencing Building.

referencing part of the composite primary key

Foreign Key Reference Composite Primary Key

You have a parent table with a composite primary key on columns (deviceID, deviceType). If you want to create a child table, you would need to:

• create one column in the child table for each column that is part of the primary key in the parent table (deviceID, deviceType)
• create a composite foreign key that references that tuple of columns to the corresponding column tuple in the parent table

Consider:

CREATE TABLE IF NOT EXISTS `data` (
    `dataID` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `deviceID` int(11) unsigned NOT NULL,
    `deviceType` int(10) unsigned NOT NULL,
    `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    PRIMARY KEY (`dataID`),
    CONSTRAINT `fk_data_pk` 
        FOREIGN KEY (`deviceID`, `deviceType`) 
        REFERENCES `devices` (`deviceID`, `deviceType`) 
        ON DELETE CASCADE
);

NB: creating a composite foreign key is functionally different than creating two foreign keys, each pointing at one of the columns in the parent table.

Given this data in the parent table:

deviceID  deviceType
1         0
2         1

If you create a separated foreign key on each column, they will allow you to insert a record in the child table with values like (1, 1), or (2, 0). The composite foreign key will not allow it, since these specific tuples do not exist in the source table.

REFERENCE ON COMPOSITE PRIMARY KEY

You need to put the constraint in a single reference:

CONSTRAINT fk_BOX FOREIGN KEY(CLIENT_CODE, BOX_CODE, BOX_NUMBER)
    REFERENCES BOX(CLIENT_CODE, BOX_CODE, BOX_NUMBER)

However, I would encourage you to put an identity int primary key on BOX, and use that for a foreign key reference. You can declare a UNIQUE constraint on BOX(CLIENT_CODE, BOX_CODE, BOX_NUMBER) to prevent duplicates.

referencing part of the composite primary key

When a subrow in one table has to be a subrow in another (referenced) table, that is an inclusion dependency (IND). Eg player team_id referencing team id (not a key in team). When there is an IND and the referenced subrow is a key, that is a foreign key (FK). Eg player person_id referencing person id (a key in person). In SQL a FOREIGN KEY declaration says that there is an IND and the referenced columns are unique. (Ie declared by either PRIMARY KEY or UNIQUE). (So it actually means "foreign superkey".) In SQL we would ideally declare an IND (when there isn't also a FK) by a CHECK constraint. (But DBMSes do not support CHECK well). Ie check for player that team_id is in team projected on id. But you tried to declare an SQL FK. It fails because, as the error message says, team id is not unique.

---

(Notice here the distinction commented between teams and their team ids. Because a team id doesn't identify a team, you should only speak of a team identified by a team id and club. OOP must distinguish not only between teams in the world and team ids (some kind of string) in the world but also "team_id" pointer/reference values of a programming language. It was commented that id" is a bad name because a team id alone doesn't identify a team. We could just use the term used in the world.)

But you actually want something stronger than both your player table team_id IND and player_id FK. Ie check for player that team_id is paired in club with a club_id that is paired in contract with a person_id that is person_id.

Such complicated constraints are actually unnecessary if your design involved, say, roster(club_id,team_id,player_id) and didn't have team_id in player and probably didn't have contract either.

No, we cannot "see" from a schema that "person references club through contract". (And that phrase is a poor way of expressing what you mean.) We cannot tell that from the tables and keys. Every table holds rows that satisfy some statement parameterized by its column names; you must give these statements. Eg for player: person [person_id] plays on a team identified by [team_id] & some club. You must also give all business rules that restrict what situations can arise. Eg: A person can be contracted to at most one club. A team belongs to at most one club. A user (nor we) cannot use the database without the statements and you (nor we) cannot determine the constraints without the statements and business rules. Write them out.

mySQL Composite Key, Primary Key, Unique Key and References to the composite key

PRIMARY KEY (CourseN, `Quarter`, DayTime),
FOREIGN KEY (CourseN) REFERENCES Course(CourseN)

In this construct, you are making a composite primary key with columns (CourseN, Quarter DayTime). This implies that any combination of values of these three columns must be unique in the table. Also, you indicate that CourseN is a foreign key that references the synonym column in table Course, so every value of column CourseN in your referring table LocationNTime must be present in the referred table Course.

This looks like a valid set up, where LocationNTime is like a dependent table of Course (like a child table or the-like).

---

PRIMARY KEY (CourseN),
UNIQUE KEY(`Quarter`, DayTime),
FOREIGN KEY (CourseN) REFERENCES Course(CourseN)

Here, CourseN is the primary key of the table, so each value in this column must be unique. But it is also a foreign key to Course. This does not really make sense in terms of normalization: if each and every record in LocationNTime relate to a unique record in Course, then why create two tables instead of storing all the data in a single record in Course?

On top of this, you are creating a unique constraint on (Quarter, DayTime), hence signaling that the combination of values in these columns unique.

This design looks flawned.

---

Consider the following dataset:

CourseN    Quarter    DayTime
1          1          14:00:00
1          2          14:10:00
1          3          14:00:00
1          4          14:10:00

The first design will allow this dataset, while the second will not, since the are duplicates in CourseN. The following dataset would not be allowed either, since there are duplicates in (Quarter, DayTime):

CourseN    Quarter    DayTime
1          1          14:00:00
2          2          14:10:00
3          1          14:00:00
4          2          14:10:00

I believe that the first design is probably what you need.

---

Finally: you seem to think that a column that has a foreign key constraint (here, LocationNTime(CourseN)) needs to be UNIQUE: this is not the case. The only thing it needs is an index (and MySQL creates it for you under the hood when you declare it anyway). It's the column that is referred by the foreign key (here Course(CourseN)) that needs to unique.

How do I reference a composite primary key with a foreign key using MySQL

This is the problem with composite PKs. In fact, your autonumber topographic_region_id will be unique and you should use that for the PK, and the FK. topographic_region sounds like it is also unique so you should add a unique index to it.

---

Related Topics