How can I link tables with a composite primary key and a single-column foreign key in MySQL?

Linking Tables with Composite Primary and Foreign Keys

In the realm of SQL database design, you may encounter situations where a table's primary key comprises multiple columns, forming a composite key. Establishing relationships between such tables using foreign keys can be a task to navigate.

Consider the given scenario involving two tables, 'tutorial' and 'group'. The 'tutorial' table features a composite primary key consisting of 'beggingTime', 'day', and 'tutorId', while the 'group' table possesses an auto-incrementing primary key 'groupId'. The question arises: how can we relate these tables seamlessly?

The MySQL documentation offers a solution: it is possible to create a foreign key mapping in 'group' referencing the composite primary key in 'tutorial'. However, this involves adding additional columns to the 'group' table, one for each corresponding primary key in 'tutorial'.

For instance, adding these columns and declaring the foreign key in the 'group' table would establish the relationship:

ALTER TABLE `group` ADD COLUMN `beggingTime` time NOT NULL;
ALTER TABLE `group` ADD COLUMN `day` varchar(8) NOT NULL;
ALTER TABLE `group` ADD COLUMN `tutorId` int(3) NOT NULL;

ALTER TABLE `group` ADD FOREIGN KEY (`beggingTime`, `day`, `tutorId`) REFERENCES `tutorial`(`beggingTime`, `day`, `tutorId`);

It is worth noting that this approach may not be optimal from a performance perspective. SQL database engines are optimized for primary keys consisting of single columns. Therefore, consider redesigning the 'tutorial' table to employ a surrogate primary key as opposed to a composite primary key. This strategy will enhance performance since it better aligns with the way SQL is structured.

The above is the detailed content of How can I link tables with a composite primary key and a single-column foreign key in MySQL?. For more information, please follow other related articles on the PHP Chinese website!