Conditional table reference

Question

I have a table tree:

+------+-------+
| id   | name  |
+------+-------+
| 0    | tree1 |
| 1    | tree2 |
| 2    | tree3 |
| 3    | tree4 |
+------+-------+

A table pen:

+------+------+
| id   | name |
+------+------+
| 0    | pen1 |
| 1    | pen2 |
| 2    | pen3 |
| 3    | pen4 |
+------+------+

And a third table task that "attaches" a task to either a tree or a pen:

+------+------+-------+
| type | id   | name  |
+------+------+-------+
| 0    | 1    | foo   |
| 0    | 2    | bar   |
| 1    | 1    | fee   |
| 1    | 2    | beer  |
+------+------+-------+

When type is 0, it means id references a tree. When type is 1, it references a pen (and so on with many different tables).

How can I do this and ensure referential integrity?

Answer 1

I'd suggest a bridge table for task-tree and anther one for task-pen.

create table tree 
(
    tree_id int primary key,
    name text
);

create table pen
(
    pen_id int primary key,
    name text
);

create table task
(
    task_id int primary key,
    name text
);

create table task_tree
(
    task_id int references task (task_id) on update cascade,
    tree_id int references tree (tree_id) on update cascade, 
    primary key (task_id, tree_id)
);

create table task_pen
(
    task_id int references task (task_id) on update cascade,
    pen_id int references pen (pen_id) on update cascade,
    primary key (task_id, pen_id)
);

insert into tree values (1, 'tree1'),(2, 'tree2'),(3, 'tree3');
insert into pen values (1, 'pen1'),(2, 'pen2'),(3, 'pen3');
insert into task values (1, 'task1'),(2, 'task2');
insert into task_tree values (1, 1),(2, 3),(1,2);
insert into task_pen values (1, 2),(2, 2);

select   tk.task_id, 
         tk.name, 
         array_agg(tr.name) as tree, 
         array_agg(pn.name) as pen
from     task tk
join     task_tree tkt
on       tkt.task_id = tk.task_id
join     task_pen tkp
on       tkp.task_id = tk.task_id
join     tree tr
on       tr.tree_id = tkt.tree_id
join     pen pn
on       pn.pen_id = tkp.pen_id
group by tk.task_id, tk.name

task_id | name  | tree          | pen        
------: | :---- | :------------ | :----------
      1 | task1 | {tree1,tree2} | {pen2,pen2}
      2 | task2 | {tree3}       | {pen2}     

dbfiddle here

Update

If, as per comments, you prefer to use a single task table with two fields, tree_id and pen_id, you can set referential integrity in this way:

I've added a check constraint that ensures that one of tree_id, pen_id is null:

alter table task 
    add constraint ck_one_in_two_must_be_null check(tree_id is null or pen_id is null);

create table tree 
(
    tree_id int primary key,
    name text
);

create table pen
(
    pen_id int primary key,
    name text
);

create table task
(
    task_id int primary key,
    name text,
    tree_id int references tree (tree_id) on update cascade on delete restrict,
    pen_id int references pen (pen_id) on update cascade on delete restrict
);

alter table task 
add constraint ck_one_in_two_must_be_null check(tree_id is null or pen_id is null);

insert into tree values (1, 'tree1'),(2, 'tree2'),(3, 'tree3');
insert into pen values (1, 'pen1'),(2, 'pen2'),(3, 'pen3');
insert into task values (1, 'task1', 1, null),(3, 'task3', null, 3);

select    tk.task_id, 
          tk.name,
          tr.tree_id,
          tr.name as tree_name,
          pn.pen_id,
          pn.name as pen_name
from      task tk
left join tree tr
on        tr.tree_id = tk.tree_id
left join pen pn
on        pn.pen_id = tk.pen_id;

task_id | name  | tree_id | tree_name | pen_id | pen_name
------: | :---- | ------: | :-------- | -----: | :-------
      1 | task1 |       1 | tree1     |   null | null    
      3 | task3 |    null | null      |      3 | pen3    

insert into task values (2, 'task2', 2, 3);

ERROR:  new row for relation "task" violates check constraint "ck_one_in_two_must_be_null"
DETAIL:  Failing row contains (2, task2, 2, 3).

insert into task values (4, 'task4', 4, null);

ERROR:  insert or update on table "task" violates foreign key constraint "task_tree_id_fkey"
DETAIL:  Key (tree_id)=(4) is not present in table "tree".

insert into task values (5, 'task5', null, 6);

ERROR:  insert or update on table "task" violates foreign key constraint "task_pen_id_fkey"
DETAIL:  Key (pen_id)=(6) is not present in table "pen".

dbfiddle here

Answer 2

Creating a table task adding a refer id field for each referred table, in this way:

• type : I prefer something as 'tree', 'pen'. Use an enum for this: ENUM('tree', 'pen');
• tree_id : int (it can be NULL), that refers to tree table;
• pen_id : int (it can be NULL), that refers to pen table;
• name : varchar;

Some test data:

+------+---------+--------+-------+
| type | tree_id | pen_id | name  |
+------+---------+--------+-------+
| tree | 1       | NULL   | foo   |
| tree | 2       | NULL   | bar   |
| pen  | NULL    | 1      | fee   |
| pen  | NULL    | 2      | beer  |
+------+---------+--------+-------+