2

I have a question about design part of my db I have 3 tables like these:

CREATE TABLTE projects (
    uuid uuid
    name text
)
CREATE TABLTE invoices (
    uuid uuid
    cost text
)
CREATE TABLTE fields (
    uuid uuid
    parent uuid REFERENCES (???) ON DELETE CASCADE ON UPDATE CASCADE,
    type varchar(255) (project or invoice)
)

is possible somehow to create one foreign key that look on more then one table and based on 2 columns (parent, type)? or do I need to do something like this?

CREATE TABLTE filds (
    uuid uuid
    project_uuid uuid REFERENCES projects(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
    invoice_uuid uuid REFERENCES invoices(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
)
2
  • 1
    Does this answer your question? Conditional Foreign Key Relationship. There is also this question whose answers offer different approaches. Basically, you have to decide if you really need different tables for projects and invoices, or a single table with a type in it. The decision often depends on whether they have similar attributes or not. Jun 22, 2020 at 15:15
  • the tables are logically different, so in my head they should be treated like different entity, and so I should not merge the 2 tables in one, but I'm open to change if it make sense, the other solution would be to use the "wordpress style" with a generic table "items" with a type column and another table "items_fields" but I'm not sure which of these 2 solution is better...do you have any suggestions? Jun 22, 2020 at 23:04

1 Answer 1

3

There are three ways:

  1. If projects and invoices have almost the same columns and are ofhen used together like “get all projects or invoices that fulfill a certain condition” or “there must be a project or invoice that...”, then it is natural to model these two objects with a single table, introducing a column type to tell them apart.

    Then you can have a regular foreign key.

  2. If they should be modeled as different tables and have different columns, introduce two foreign key columns in fields, ideally with a check constraint:

     CHECK (parent_invoice IS     NULL AND parent_project IS NOT NULL OR
            parent_invoice IS NOT NULL AND parent_project IS     NULL)
    
  3. A hybrid solution would be

     CREATE TYPE p_or_i AS ENUM (
        'project',
        'invoice'
     );
    
     CREATE TABLE project_or_invoice (
        id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
        type p_or_i NOT NULL,
        /* other common columns */,
        PRIMARY KEY (id, type)
     );
    
     CREATE TABLE project (
        id bigint NOT NULL,
        type p_or_i GENERATED ALWAYS AS ('project') STORED NOT NULL,
        /* specific columns */,
        PRIMARY KEY (id, type),
        FOREIGN KEY (id, type) REFERENCES project_or_invoice
     );
    
     CREATE TABLE invoice (
        id bigint NOT NULL,
        type p_or_i GENERATED ALWAYS AS ('invoice') STORED NOT NULL,
        /* specific columns */,
        PRIMARY KEY (id, type),
        FOREIGN KEY (id, type) REFERENCES project_or_invoice
     );
    

    Then you would reference project_or_invoice in your foreign key.

2
  • thanks for the answer, I have a just a question about solution 2, is this acting like a foreign key? If I delete the parent row, will these row deleted too? thanks Jun 26, 2020 at 14:25
  • That's not how a foreign key works unless you define it with ON DELETE CASCADE. But sure, if you define both foreign keys like that, that's how it will work. I didn't spell out the foreign keys in my second solution; I though that part was obvious. Jun 26, 2020 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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