2

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?

  • Look at Inheritance. Using it you could to create one base table for references and inherit from it both tree and pen tables. – Abelisto Feb 3 '18 at 18:41
2

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

  • This looks like a very good solution ! However, in my case I want to prevent a task to be to multiple things. It's either one pen, either one tree. How can I ensure referential integrity with those constraints ? – Narann Feb 4 '18 at 13:17
  • @Narann I've updated the answer – McNets Feb 4 '18 at 14:28
  • Thanks ! My problem is still with task_id 2 which is connected to multiple entities (tree2 AND pen3). insert into task values (2, 'task2', 2, 3); should raise an error. How can I ensure mutual exclusion? – Narann Feb 4 '18 at 16:52
  • I've added a check constraint, have a look – McNets Feb 4 '18 at 17:02
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  |
+------+---------+--------+-------+
  • Thanks, but how can I ensure referential integrity from a PostgreSQL stand point ? – Narann Feb 3 '18 at 15:02
  • Linking each referential field to respective table. task.tree_id to tree.id and task.pen_id to pen.id, in NO_ACTION or CASCADE or RESTRICT mode (as you need). – Fabrizio Caldarelli Feb 3 '18 at 15:07
  • 2
    But | tree | 1 | 1 | foo | will still be "valid" for PostgreSQL while it doesn't make sense in my use case. No? – Narann Feb 3 '18 at 15:17
  • Exactly, it is only a developer error, but referential integrity is respected. – Fabrizio Caldarelli Feb 3 '18 at 15:20
  • i don't like this approach very much because if you need more tables (say 10), you'll need 10 columns. The constraint can be enforced by the way, with CHECK (pen_id IS NOT NULL AND tree_id IS NULL or pen_is IS NULL AND tree_is IS NOT NULL). Or a bit more compact: CHECK ( (pen_id IS NOT NULL)::int + (tree_id IS NOT NULL)::int = 1). The second way is more easier to be changed for many (10) columns. – ypercubeᵀᴹ Feb 3 '18 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.