Foreign Key Reference Composite Primary Key

Question

The database is going to store information about hardware devices and their gathered data. I created a devices table to store the available hardware devices:

CREATE TABLE IF NOT EXISTS `devices` (
  `deviceID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `deviceType` int(10) unsigned NOT NULL,
  `updateFrequency` int(10) unsigned NOT NULL,
  PRIMARY KEY (`deviceID`,`deviceType`)
)

The deviceID will correspond to a real hardware id (from 1 to 12). Since there are two types of hardware devices I thought it would be appropriate to create a deviceType which will be either 0 or 1 depending on which hardware device and make a composite primary key.

To store that data I created another table.

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

The problem is obviously that I cannot reference the composite key in one column inside data. Would it make sense to create an additional column inside data for deviceType and foreign key reference that as well or would it make more sense to assign deviceID and deviceType inside devices to another id and reference that inside data?

Thanks in advance!

Answer 1

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.