CODEWITHSUNDEEP
postgresqldatabase-designforeign-keys
shadeglare
shadeglare

Reputation: 7536

Design constraints in SQL tables

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.

Upvotes: 0

Views: 121

Answers (1)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

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

Upvotes: 1

Related Questions