PostgreSQL ensure data integrity for shared primary key-CodePudding

Let's say we want to use globally unique id and find the type of entity using it:

CREATE TABLE identity (
   id serial PK NOT NULL,
   type IdentityEnum NOT NULL,
   UNIQUE (id, type)
);

CREATE TABLE user (
   id integer PK NOT NULL REFERENCES identity (id),
   type IdentityEnum NOT NULL,
   UNIQUE (id, type)

ALTER TABLE user 
    add constraint identity_fk
    foreign key (id, type) 
    REFERENCES identity (id, type);
);

CREATE TABLE animal (
   id integer PK NOT NULL REFERENCES identity (id),
   type IdentityEnum NOT NULL,
   UNIQUE (id, type)

ALTER TABLE animal 
    add constraint identity_fk
    foreign key (id, type) 
    REFERENCES identity (id, type);
);

To ensure that user or animal tables will have one to one relation to identity:

it's will not be possible by more that one table to point at the same identity PK

We are forced to add additional type column for each table, add UNIQUE (id, type) and add FK from each table to identity.

Questions:

Is there another way to design database with a shared by multiple tables id without the need for type?
How to do joins if you have only id and don't know the type?

CodePudding user response：

You do not need the type columns in any table. The type will appear in a view by the fact that the complementary data are stored into the animal or user table. This is known as an inherited data modeling. The "IDENTITY" table is the father table, while user and animal are children tables.

But to make it working properly you need to add triggers for INSERT / UPDATE that exclude the same ID velue to be present in the two child tables.

Use the view