Postgresql – Uniqueness constraint in union of two columns’ values

postgresqlpostgresql-9.5unique-constraint

Using postgresql 9.5, assume I have a table:

CREATE TABLE t (
 id INT,
 primary TEXT,
 secondary TEXT
)

I want to guarantee that the union of values in primary and secondary does not contain duplicates. Is this doable via index/checks or do I have to go to a trigger?

For example,

INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'b');
INSERT INTO t(id, primary, secondary) VALUES(1, 'x', 'y');
INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'z');
INSERT INTO t(id, primary, secondary) VALUES(1, 'z', 'a');

The latter two INSERTs should fail.

Best Answer

A trigger solution might work but I'd prefer to normalize the design and have simple constraints:

The table that is now:

CREATE TABLE t (
 id INT PRIMARY KEY,
 t_primary TEXT,
 t_secondary TEXT
) ;

would become:

CREATE TABLE tt (
 id INT,
 t_no SMALLINT NOT NULL,
 t_text TEXT,
 PRIMARY KEY (id, t_no)
 UNIQUE (t_text),
 CHECK (t_no IN (1,2))
) ;

Then your inserts 

INSERT INTO t (id, t_primary, t_secondary) VALUES (1, 'a', 'b');
INSERT INTO t (id, t_primary, t_secondary) VALUES (2, 'x', 'y');
INSERT INTO t (id, t_primary, t_secondary) VALUES (3, 'a', 'z');
INSERT INTO t (id, t_primary, t_secondary) VALUES (4, 'z', 'a');

would become:

INSERT INTO tt (id, t_no, t_text) VALUES (1, 1, 'a'), (1, 2, 'b');
INSERT INTO tt (id, t_no, t_text) VALUES (2, 1, 'x'), (2, 2, 'y');
INSERT INTO tt (id, t_no, t_text) VALUES (3, 1, 'a'), (3, 2, 'z');  -- will fail
INSERT INTO tt (id, t_no, t_text) VALUES (4, 1, 'z'), (4, 2, 'a');  -- will fail

Finally you can have the original output with:

CREATE VIEW t AS
SELECT 
    a.id, 
    a.t_text as t_primary,
    b.t_text as t_secondary,
FROM tt AS a
  JOIN tt AS b
    ON a.id = b.id AND a.t_no = 1 AND b.t_no = 2 ;

SELECT *
FROM t ;
Related Question