2

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the referencing-field combinations should not be required to be unique. I'm working in PostgreSQL 9.4, in case that gives me a solution that wouldn't otherwise exist.

For example, let's say I have these tables:

CREATE TABLE a (
  a_id serial NOT NULL PRIMARY KEY
  ,name text NOT NULL
);

CREATE TABLE b (
  b_id serial NOT NULL PRIMARY KEY
  ,name text NOT NULL
  ,a_id integer REFERENCES a (a_id)
);

CREATE TABLE c (
  c_id serial NOT NULL PRIMARY KEY
  ,name text NOT NULL
  ,b_id integer NOT NULL REFERENCES b (b_id)
  ,a_id integer
);

Now suppose that a has data

a_id | name
-----+----------
   1 | 'First A'

and b has data

b_id | name       | a_id
-----+------------+-----
   1 | 'First B'  |    1
   2 | 'Second B' | NULL

I would like to alter c (and other tables as needed) so that the following will all succeed,

INSERT INTO c (b_id, a_id, name) VALUES (1, 1, 'First C');
INSERT INTO c (b_id, a_id, name) VALUES (2, NULL, 'Second C');
INSERT INTO c (b_id, a_id, name) VALUES (1, 1, 'Another 1-1 C');

but both of these will fail,

INSERT INTO c (b_id, a_id, name) VALUES (1, NULL, 'Bad C one');  --b=1 must have a=1
INSERT INTO c (b_id, a_id, name) VALUES (2, 1, 'Bad C two');  --b=2 must have a=NULL

If b and c did not allow a_id to be null, then I could just

ALTER TABLE b
  ADD UNIQUE (b_id, a_id);
ALTER TABLE c
  ADD FOREIGN KEY (b_id, a_id)
  REFERENCES b (b_id, a_id);

With the null a_id values though, this allows the first bad insert to succeed, because the null value in the a_id column isn't checked against the value of any rows in b.

I also tried using MATCH FULL, but this prevents the insertion of null values in the a_id column entirely, even though it is nullable, since b_id cannot also be null.


What is the best way to accomplish what I'm trying to do?

  • No, not possible with nulls. Normalize the database, don't use nullable columns, if you want to have referencial integrity. – ypercubeᵀᴹ May 16 '15 at 22:26
  • But I wonder, why do you need the a_id in the c table? What's the problem i f you remove it - and reference only b (b_id) ? – ypercubeᵀᴹ May 16 '15 at 22:29
  • If a is a user table, then I want each user to see all rows of b that have an a_id that is either null or that matches the user's id. c contains separate objects that each require one b, but which should not be allowed to reference rows of b that belong to a different user. – Michael Underwood May 16 '15 at 22:37
  • Is there a better way to be accomplishing the goal of having some rows visible to all, and others only to one user? I couldn't think of another way that didn't involve a second nearly identical table for each of b and c. – Michael Underwood May 16 '15 at 22:39
  • OK but you haven't answered. Why removing c (a_id) doesn't solve your problem? You can always find the relative user (a_id) by joining to b. – ypercubeᵀᴹ May 17 '15 at 1:12

Your Answer

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

Browse other questions tagged or ask your own question.