Unique references in Postgres

Question

I need to implement a wrapper of items (like a folder or a list) that holds items. The thing is - an item can be of two types (Action or Event)

For example imagine a user created this list:

Folder (i.e - "wrapper") : 
 1. Action A
 2. Action B
 3. Event X
 4. Action C
 5. Event Y

Both "INHERIT" and simply creating referencing tables impose the same limitation - uniqueness of an item.

For example :

CREATE TABLE test_items(
    id          int UNIQUE NOT NULL generated always as identity,
    wrapper_id  unsigned_int NOT NULL, -- reference to folder/list id
    
    order_index unsigned_smallint NOT NULL, -- 1, 2, 3, ...
    
    PRIMARY KEY (id, wrapper_id),
    FOREIGN KEY (wrapper_id) references test_item_wrappers (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX test_items_order_idx on test_items (wrapper_id, order_index);


CREATE TABLE test_item_actions(
    item_id  unsigned_int,  
    is_done  boolean,
    notes    boolean,
    
    PRIMARY KEY (item_id),
    FOREIGN KEY (item_id) REFERENCES test_items (id)
);


CREATE TABLE test_item_events(
    item_id  unsigned_int,  
    dates    daterange,
    
    PRIMARY KEY (item_id),
    FOREIGN KEY (item_id) REFERENCES test_items (id)
);


CREATE DOMAIN unsigned_int AS int
   CHECK(VALUE >= 0 AND VALUE < 2147483647);

But this design means that I can create both an Action and an Event that reference the same item_id. Which is forbidden. An item can only be of one type.

The easiest solution would be to simply have one single table for both types (that is, combine all columns to one single table of test_items). This would also help greatly with all the relevant queries (inserting, updating, selecting, deleting).

But in reality, my "actions" can have a lot of columns, while an "event" has only 2 columns. So it just doesn't seem right.

Answer 1

This looks a bit out of proper data model design to me, but if you're sure there's no other way to go around your data model, then having triggers that enforce uniqueness (and RAISE when it is violated) on BEFORE INSERT/UPDATE for both test_item_actions and test_item_events is the way to go here.