How to Create a Foreign Key Relationship with a MySQL Composite Primary Key?

Associating Composite Primary Keys in MySQL

In the given scenario, you have two tables, tutorial and group, where tutorial has a composite primary key (beggingTime, day, and tutorId). You aim to establish a relationship between these tables by creating a field in group that references the composite primary key in tutorial.

According to MySQL documentation, establishing foreign key mappings to composite keys is possible. To accomplish this:

1. Add Columns to group: Create three columns in the group table to correspond to the composite primary key fields in tutorial: beggingTime, day, and tutorId.
2. Define Foreign Key Relationship: Add the following line to the group table definition:

FOREIGN KEY (`beggingTime`,`day`,`tutorId`) REFERENCES tutorial(`beggingTime`,`day`,`tutorId`)

This will establish a foreign key relationship between the composite primary key in tutorial and the corresponding fields in group.

However, it's important to note that experts recommend re-architecting the tutorial table to use a single primary key (e.g., an identity surrogate key). This enhances performance because SQL is optimized for relationships involving primary keys, rather than composite keys.

The above is the detailed content of How to Create a Foreign Key Relationship with a MySQL Composite Primary Key?. For more information, please follow other related articles on the PHP Chinese website!