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

score 3 · Answer 1 · answered Jun 23 '20 at 03:23

There are three ways:

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.
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)
```

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.

foreign key on multiple tables based on multiple columns

1 Answers1