2

example

For the model above I want to make it so a robot must have a power supply and motor with matching voltages. Is there a way to constrain the robot entity so it cannot have a power supply and motor that don't match voltages? Or is there a better way to model this so I don't need a constraint?

  • Side note: Unless you need name to be the key, you could have a compound key over motor and power_supply to ensure that you don't have two entries for the same two parts. If you had a Parts table, you could have a part_type column and a constraint on INSERT into robots which could require voltage to be the same across parts. – Thronk Nov 19 '15 at 0:56
2

Here is the definitive answer for which I can't take credit. That belongs to ypercube who has given me an insight into the power of FOREIGN KEYs with multiple fields. I've taken the liberty of removing most of the previous discussion - it can be followed through the edits.

My first (primitive) answer would have worked, but the DDL below is so much more elegant - it's like comparing the Lascaux cave paintings with Van Gogh's masterpieces.

Definitive DDL/DML with test (using MySQL).

Power supply table.

CREATE TABLE power_supply
(
  part_no INT PRIMARY KEY,
  weight INT,
  voltage INT,
  UNIQUE(part_no, voltage)
);

Motor table.

CREATE TABLE motor
(
  part_no INT PRIMARY KEY,
  model VARCHAR(45),
  voltage INT,
  UNIQUE (part_no, voltage)
);

Robot table.

CREATE TABLE robot
(
  name VARCHAR(30) PRIMARY KEY,
  power_supply INT,
  motor INT,
  voltage INT,
  FOREIGN KEY (power_supply, voltage)
        REFERENCES power_supply (part_no, voltage),
  FOREIGN KEY (motor, voltage)
        REFERENCES motor (part_no, voltage)
);

DML.

INSERT INTO power_supply VALUES(1, 25, 10);
INSERT INTO power_supply VALUES(2, 25, 10);
INSERT INTO power_supply VALUES(3, 15, 15);
INSERT INTO power_supply VALUES(4, 25, 15);
INSERT INTO power_supply VALUES(5, 25, 15);
INSERT INTO power_supply VALUES(6, 25, 20);
INSERT INTO power_supply VALUES(7, 25, 20);
INSERT INTO power_supply VALUES(8, 25, 25);

INSERT INTO motor VALUES(1, 'Model Name', 10);
INSERT INTO motor VALUES(2, 'Model Name', 10);
INSERT INTO motor VALUES(3, 'Model Name', 15);
INSERT INTO motor VALUES(4, 'Model Name', 15);
INSERT INTO motor VALUES(5, 'Model Name', 20);
INSERT INTO motor VALUES(6, 'Model Name', 20);
INSERT INTO motor VALUES(7, 'Model Name', 25);

Test.

INSERT INTO robot VALUES('Robo Name', 2, 6, 15); -- Voltages don't match, update fails
INSERT INTO robot VALUES('Robo Name', 2, 2, 10); -- Voltages match, update succeeds

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.