0

In the following scenario, I want to make sure that if you take a single entry of d, you can assume that it's associated b, and c have the same a_id:

CREATE TABLE A (
  id INT NOT NULL,
);

CREATE TABLE B (
  id INT NOT NULL,
  a_id INT NOT NULL,
  CONSTRAINT fk_b_a FOREIGN KEY (a_id) REFERENCES A (id) ON DELETE NO ACTION ON UPDATE CASCADE,
);

CREATE TABLE C (
  id INT NOT NULL,
  a_id INT NOT NULL,
  CONSTRAINT fk_c_a FOREIGN KEY (a_id) REFERENCES A (id) ON DELETE NO ACTION ON UPDATE CASCADE,
);

CREATE TABLE D (
  id INT NOT NULL,
  b_id INT NOT NULL,
  c_id INT NOT NULL,
  CONSTRAINT fk_d_b FOREIGN KEY (b_id) REFERENCES B (id) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT fk_d_c FOREIGN KEY (c_id) REFERENCES C (id) ON DELETE NO ACTION ON UPDATE CASCADE,
);

Is there a way to ensure this with a check constraint? Or maybe my database design is flawed?

hgiesel
  • 5,430
  • 2
  • 29
  • 56
  • If this is with MSSQL It is doable with a function, check this post. If making a before insert trigger is an option, it should be easier. https://stackoverflow.com/questions/63401557/sql-constraint-to-check-the-value-of-another-tables-column – Agustin Pazos Oct 31 '22 at 02:33
  • If you're able to change the model, including `a_id` in `D` and making that part of the foreign key columns/referenced columns enforces the required constraint. – Damien_The_Unbeliever Oct 31 '22 at 07:22

0 Answers0