Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE A(a VARCHAR(10), b VARCHAR(10), d DATE , UNIQUE(a,b))

CREATE TABLE

INSERT INTO A(a, b, d)
VALUES (NULL, NULL, NOW()),('a', NULL, NOW()),(NULL, 'b', NOW()),('c', 'b', NOW())

INSERT 0 4

CREATE TABLE B(id INT PRIMARY KEY, ref_a VARCHAR(10), ref_b VARCHAR(10));

CREATE TABLE

SELECT * FROM A;
SELECT * FROM B;

a
b
d

null
null
2025-03-05

a
null
2025-03-05

null
b
2025-03-05

c
b
2025-03-05

SELECT 4

id
ref_a
ref_b

SELECT 0

ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b) REFERENCES A(a,b) MATCH SIMPLE;

ALTER TABLE

INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, 'b');
INSERT INTO B(id, ref_a, ref_b) VALUES (2, NULL, 'x');

INSERT 0 1

INSERT 0 1

SELECT * FROM A;
SELECT * FROM B;

a
b
d

null
null
2025-03-05

a
null
2025-03-05

null
b
2025-03-05

c
b
2025-03-05

SELECT 4

id
ref_a
ref_b

1
null
b

2
null
x

SELECT 2

ALTER TABLE B DROP CONSTRAINT B_Fk;
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b) REFERENCES A(a,b) MATCH PARTIAL;

ERROR:  MATCH PARTIAL not yet implemented
LINE 2: ...B_Fk FOREIGN KEY (ref_a, ref_b) REFERENCES A(a,b) MATCH PART...
                                                             ^

DELETE FROM B;
ALTER TABLE B DROP CONSTRAINT IF EXISTS B_Fk;
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b) REFERENCES A(a,b) MATCH FULL;

DELETE 2

ALTER TABLE

ALTER TABLE

INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, 'b');
/*
ERROR:  insert or update on table "b" violates foreign key constraint "b_fk"
DETAIL:  MATCH FULL does not allow mixing of null and nonnull key values.
*/

ERROR:  insert or update on table "b" violates foreign key constraint "b_fk"
DETAIL:  MATCH FULL does not allow mixing of null and nonnull key values.

INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, NULL);

INSERT 0 1

SELECT * FROM A;
SELECT * FROM B;

a
b
d

null
null
2025-03-05

a
null
2025-03-05

null
b
2025-03-05

c
b
2025-03-05

SELECT 4

id
ref_a
ref_b

1
null
null

SELECT 1
a	b	d
null	null	2025-03-05
a	null	2025-03-05
null	b	2025-03-05
c	b	2025-03-05