Design constraints in SQL tables

Question

I'm designing the database and have a problem with how to create a specific constraint. Here's an simple version of the problem:

-- pk, fk full descriptions omitted for for brevity

create table labs (
    -- pk
    id uuid
);

create table tests (
    -- pk
    id uuid,

    -- lab_id + code = unique
    lab_id uuid, --FK to labs
    code character varying
);

create table panels (
    -- pk
    id uuid,

    -- lab_id fk to labs
    lab_id uuid
);

create table panels_tests (
    -- pk
    id uuid,

    -- how to ensure panel_id and test_id have reference to entities with the same lab code?
    panel_id uuid,
    test_id uuid
);

So is there a way to make sure panel_id and test_id in the panels_tests table have reference to entities with the same lab code? Or may be it's better to be done through an app business logic?

Thanks.

Application logic should validate it before trying to insert or update. You could also add a trigger in insert + update to panels_tests that also validates and throws an error if they don't match. — simon at rcl
– simon at rcl, Commented Jul 25, 2019 at 10:18
Tried to resolve this, but not sure what is a panel? A set of tests? Group of people? Some hardware? Could you define that? — Damir Sudarevic
– Damir Sudarevic, Commented Jul 27, 2019 at 21:35
panel is a set of tests. panel's always associated with the lab. so I want to make sure panel_test lab and panel lab are the same to prevent lab mismatching. — shadeglare
– shadeglare, Commented Jul 28, 2019 at 11:59

Damir Sudarevic · Accepted Answer · 2019-07-28 07:37:04Z

Given the question, this should work. Admittedly I do not fully understand the business domain, so hope there are no logical errors. For more details you would need to explicitly state (verbalize) constraints from the "business domain"

lab {LAB_ID}
 PK {LAB_ID}


test {TEST_ID, LAB_ID,TEST_CODE}
  PK {TEST_ID}
  AK {LAB_ID, TEST_CODE}
  SK {TEST_ID, LAB_ID}
  FK {LAB_ID} REFERENCES lab {LAB_ID}


panel {PANEL_ID, LAB_ID}
   PK {PANEL_ID}
   SK {PANEL_ID, LAB_ID}
   FK {LAB_ID} REFERENCES lab {LAB_ID}


panel_test {PANEL_ID, TEST_ID, LAB_ID}
        PK {PANEL_ID, TEST_ID}
       FK1 {PANEL_ID, LAB_ID} REFERENCES panel {PANEL_ID, LAB_ID}
       FK2 {TEST_ID,  LAB_ID} REFERENCES test  {TEST_ID,  LAB_ID}

Or if you prefer single PK for panel_test, just add it:

panel_test {PNL_TST_ID, PANEL_ID, TEST_ID, LAB_ID}
        PK {PNL_TST_ID}
        AK {PANEL_ID, TEST_ID}
       FK1 {PANEL_ID, LAB_ID} REFERENCES panel {PANEL_ID, LAB_ID}
       FK2 {TEST_ID,  LAB_ID} REFERENCES test  {TEST_ID,  LAB_ID}

Note that those super-keys (SK) are logically redundant, but are needed for SQL as "target" for FKs.

Notes:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Collectives™ on Stack Overflow

Design constraints in SQL tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related