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

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.