foreign key on multiple tables based on multiple columns

Question

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,
)

• Laurenz Albe: 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.
• francesco.venica: 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?

Answer 1

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.

• francesco.venica: 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
• Laurenz Albe: 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.