CODEWITHSUNDEEP
mariadb
Reppin Frost
Reppin Frost

Reputation: 39

Check constraint referring to other table

I have three table

Processor with columns Name and Socket.
Motherboard with columns Name and Socket.
Build with columns Processor[references Processor(Name)] and Motherboard[references Motherboard(Name)]

I need to ensure that the Processor and Motherboard being entered into Build have the same Socket. Since Check Constraints can't refer other values I am looking for alternatives. I know you can use UDF or Triggers for this but I am beginner and would like some example code to help me out.

Thanks.

Upvotes: 1

Views: 75

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

No trigger needed. You can do this with foreign key constraints.

  1. Add a UNIQUE KEY on Processor for the two columns, Name and Socket.
  2. Add a UNIQUE KEY on Motherboard for the two columns, Name, and Socket.
  3. Make your FOREIGN KEYs in Build reference the two columns. Give just one column to Build called Socket. Use it in both foreign keys. Therefore it must be the same.

Here's what it looks like:

CREATE TABLE `Processor` (
  `name` varchar(20) NOT NULL,
  `socket` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name` (`name`,`socket`)
);

CREATE TABLE `Motherboard` (
  `name` varchar(20) NOT NULL,
  `socket` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name` (`name`,`socket`)
);

CREATE TABLE `Build` (
  `Processor` varchar(20) DEFAULT NULL,
  `Motherboard` varchar(20) DEFAULT NULL,
  `Socket` varchar(20) DEFAULT NULL,
  CONSTRAINT `build_ibfk_1` FOREIGN KEY (`Processor`, `Socket`)
    REFERENCES `processor` (`name`, `socket`),
  CONSTRAINT `build_ibfk_2` FOREIGN KEY (`Motherboard`, `Socket`)
    REFERENCES `Motherboard` (`name`, `socket`)
);

This enforces the business rule you wanted. The foreign keys require that Build.Socket reference a socket in both Processor and Motherboard. There can only be one value of Build.Socket on a given row in Build. Therefore the socket must be the same in both tables.

Upvotes: 1

Related Questions