Add constraint based on foreign key-CodePudding

I have this two tables: Type and Device.

Type

CREATE TABLE `Type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(250) NOT NULL,
  `description` VARCHAR(250) NOT NULL,
  `type` VARCHAR(250) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
)

Device

CREATE TABLE `Device` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `serial` VARCHAR(255) NOT NULL,
  `name` VARCHAR250) DEFAULT NULL,
  `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `idType` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`serial`,`idType`),
  FOREIGN KEY (`idType`) REFERENCES `Type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

Now I need to create a table as follow:

CREATE TABLE `Test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idDeviceOne` INT NOT NULL,
  `idDeviceTwo` INT NOT NULL,
  `date` DATETIME DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`idDeviceOne`) REFERENCES `Device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`idDeviceTwo`) REFERENCES `Device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
)

In this table I need to have this constraint: idDeviceOne and idDeviceTwo as to reference to devices with the SAME idType. How can I add this type of constraint?

CodePudding user response：

You can add a UK to Device table

UNIQUE KEY (`idType`, `id`),

and reference it from Test table

CREATE TABLE `Test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idDeviceOne` INT NOT NULL,
  `idDeviceTwo` INT NOT NULL,
  `idType` INT NOT NULL,
  `date` DATETIME DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`idType`, `idDeviceOne`) REFERENCES `Device` (`idType, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`idType`, `idDeviceTwo`) REFERENCES `Device` (`idType, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
)

CodePudding user response：

CREATE TRIGGER tr_bi_check_devices_types
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
IF NOT EXISTS ( SELECT NULL
                FROM Device t1
                JOIN Device t2 USING (idType)
                WHERE t1.id = NEW.idDeviceOne
                  AND t2.id = NEW.idDeviceTwo ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE = 'The devices must have the same type.';
END IF;
END

and the same trigger BEFORE UPDATE.