With a foreign composite key, it possible to reference a column and a fixed value?

Question

Following is my use case:

I have 4 tables:

CREATE TABLE A
(
  name character(20) NOT NULL,
  id integer NOT NULL,
  CONSTRAINT a_pkey PRIMARY KEY (id)
)

CREATE TABLE B
(
  name character(20) NOT NULL,
  id integer NOT NULL,
  CONSTRAINT b_pkey PRIMARY KEY (id)
)

CREATE TABLE C
(
  name character(20) NOT NULL,
  id integer NOT NULL,
  CONSTRAINT c_pkey PRIMARY KEY (id)
)

CREATE TABLE X
(
  type character(20) NOT NULL,
  other_id integer NOT NULL,
  id integer NOT NULL,
  CONSTRAINT "X_PK" PRIMARY KEY (id)
)

the "other_id" in the table X can be the "id" of any of the table A, B or C. The "type" column in table X is suppose to be indicating which of the tables A,B or C "id" is stored in in "other_id"

example data from table x:

type  other_id  id
"A"    1         1
"B"    1         2
"C"    1         3
"A"    2         4

Trying to create composite FKs between table X-A, X-B and X-C as follows using a fixed value

ALTER TABLE x
ADD CONSTRAINT X_A_FK FOREIGN KEY (other_id, type) REFERENCES a (id, 'A') ON DELETE CASCADE

but I get this error (same problem for all the FKs):

ERROR: syntax error at or near "'A'"

My question is, with a foreign composite key, it possible to reference a column and a fixed value? If not What would be a better way of approaching this problem?

score 2 · Accepted Answer · edited May 23 '17 at 12:00

I would suggest an alternative design:

CREATE TABLE a (
  a_id integer PRIMARY KEY -- pk is NOT NULL automatically
 ,a    text NOT NULL
);

CREATE TABLE b ( ...);
CREATE TABLE c ( ...);

CREATE TABLE x (
  x_id integer PRIMARY KEY        -- or maybe a serial?
 ,a_id integer REFERENCES a(a_id) -- can be NULL
 ,b_id integer REFERENCES b(b_id)
 ,c_id integer REFERENCES c(c_id)
);

The major point is to have three columns (a_id, b_id, c_id) instead of two in your design (other_id, type). Contrary to what one might think, this needs less disk space, while being cleaner and simpler. You don't need additional constraints or indices to enforce referential integrity.
NULL storage is cheap.

If you want to enforce, that at most one of (a, b, c) can be linked at a time, add a CHECK constraint:

ALTER TABLE x ADD CONSTRAINT x_max1_fk
CHECK (a_id IS NULL AND b_id IS NULL
    OR b_id IS NULL AND c_id IS NULL
    OR a_id IS NULL AND c_id IS NULL)

Seems lengthy, but is very cheap and simple.

If you want to enforce that exactly one of (a, b, c) must be linked at a time, modify to:

ALTER TABLE x ADD CONSTRAINT x_exactly1_fk
CHECK (a_id IS NULL AND b_id IS NULL AND c_id IS NOT NULL
    OR b_id IS NULL AND c_id IS NULL AND a_id IS NOT NULL
    OR a_id IS NULL AND c_id IS NULL AND b_id IS NOT NULL)

For more than just a few columns I would use:

ALTER TABLE x ADD CONSTRAINT x_exactly1_fk
CHECK ((a_id IS NULL)::int
     + (b_id IS NULL)::int
     + (c_id IS NULL)::int = 1)  -- or <= 1 for the former case

Note how I avoid the column names id and name. Use descriptive names instead, this makes your life easier when joining a couple of tables - which you will have to do a lot with a design like this and which is what you do in a relational database.

And I use text instead of varchar(20). Why?

I would also consider the ON UPDATE CASCADE and possibly ON DELETE CASCADE modifiers for the foreign keys.

Thank you for your respond. I was considering adding extra null-able columns but was not sure if that is a good practice, because if there will be more tables such as A, B and C in the future, I will need to make more null-able columns. I do appreciate your CHECK suggestions, very handy and clean for insuring the data integrity. — Ida N, Dec 10 '13 at 23:18
@IdaN: If you have dozens of referenced tables and only a single one of them applies, `(id, type)` becomes more favorable. I would ask the question, though, whether you couldn't bundle your referenced tables into one (intermediary) table or use [inheritance](http://www.postgresql.org/docs/current/interactive/ddl-inherit.html) ... — Erwin Brandstetter, Dec 10 '13 at 23:27
In my case I only have few tables and your current solution perfectly applies. I only asked the question to know your opinion on what would be a better approach on a much larger case than couple of tables. — Ida N, Dec 10 '13 at 23:34

score 0 · Answer 2 · answered Dec 10 '13 at 20:21

0

Per the error you're getting, it's not possible. But you've at least two options to achieve the same:

You could add a check constraint on type and enforce a foreign key on (other_id) or (other_id, type) instead.
You could add constraint triggers that enforces the pseudo-foreign key directly.

answered Dec 10 '13 at 20:21

Denis de Bernardy

75,850
13
131
154

Thank you for your fast reply, would you please elaborate a little bit more on your first solution, I am not sure how to implement it. I probably should add I needed 3 Fks, I only mentioned one in my question because the very first one I tried to make, failed. – Ida N Dec 10 '13 at 20:33

With a foreign composite key, it possible to reference a column and a fixed value?

2 Answers2