CODEWITHSUNDEEP
mysqldatabaseforeign-keysrdbmscomposite-primary-key
James
James

Reputation: 337

Only one key from composite primary key as foreign key

In this database, key1 & key2 make up the composite primary key of table4, but i'm able to add a foreign key to table3 that comprise just key1.

Why MySQL allows this? Does the above database design make no sense at all?

Upvotes: 2

Views: 4565

Answers (2)

philipxy
philipxy

Reputation: 15118

This answer takes the question's "add a foreign key to table3" to mean that a FK (foreign key) was added in table3 referencing one of the columns of the composite PK (primary key) of table4. In standard SQL a FK can reference a proper/smaller subset of a PK.

This other answer presumably takes "add a foreign key to table3" to mean that a FK was added in table4 with one of the columns of the PK referencing table3. A FK column set in a table is independent of any PK or UNIQUE declarations in it.

In standard SQL a FK can reference a proper/smaller subset of a PK.

The referenced column list must be declared PRIMARY KEY or UNIQUE. (PRIMARY KEY creates a UNIQUE NOT NULL constraint.) (The constraint has to be explicit, even though any set of NOT NULL columns containing a set that is UNIQUE has to be unique.)

Unfortunately MySQL lets you declare a FK referencing a column list that is not UNIQUE. Even though such a FK or one referencing non-NULL columns (OK in standard SQL) is not implemented properly, and the documentation itself advises not doing it:

The handling of foreign key references to non-unique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only keys that are both UNIQUE (or PRIMARY) and NOT NULL.

(You can ponder just what are and are not the well-defined operations, since the documentation doesn't actually clarify.)

1.8.2.3 Foreign Key Differences
13.1.18 CREATE TABLE Syntax
13.1.18.6 Using FOREIGN KEY Constraints

PS Re relational vs SQL

In the relational model a FK references a CK (candidate key). A superkey is a unique column set. A CK is a superkey containing no smaller superkey. One CK can be called the PK (primary key). When a column set's values must appear elsewhere we say there is an IND (inclusion dependency). A FK is an IND to a CK. When an IND is to a superkey we could call that a "foreign superkey".

An SQL PK or UNIQUE NOT NULL declares a superkey. It is a CK when it does not contain a smaller column set declared as SQL PK or UNIQUE NOT NULL. SQL FK declares a foreign superkey. So an SQL PK might actually be a relational PK (hence CK) and a UNIQUE NOT NULL might actually be a CK. An SQL FK to one of these actually is a relational FK.

Upvotes: 2

fancyPants
fancyPants

Reputation: 51868

Foreign keys are not dependent of primary keys at all. In fact, they have nothing to do with primary keys.

A primary keys single purpose is to identify a row uniquely.

A foreign keys purpose is to make sure that for each entry in the referencing table (the child table) there must be an entry in the referenced table (the parent table). It's only a technical requirement in MySQL that there must be an index (not necessarily a primary key or a unique key, a simple index suffices) on the referenced column.

Therefore your design makes sense, yes.

Upvotes: 0

Related Questions