title: Foreign Key Reference Composite Primary Key
body: <p>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:</p>
  
  <pre><code>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`)
  )
  </code></pre>
  
  <p>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.</p>
  
  <p>To store that data I created another table. </p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>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?</p>
  
  <p>Thanks in advance!</p>