Postgresql constraint on 2 columns, so "col_a" cannot share any values of "col_b"

user_8044561 0
Viewed 197

I am building a database that has system barcodes, as well as the option for custom barcodes. I have a unique constraint on custom_barcode and system_barcode.

What I would like to do is have it so if custom_barcode has a value of “123”, no other row can have custom_barcode OR system_barcode with a value of “123". I am not trying to make a 2-column unique constraint where if custom_barcode is "123" and system_barcode is "456", that combination cannot exist again. Is it possible to achieve this? I'm wondering if this needs to be achieved with a trigger.

create-table unique-constraint postgresql arrays sql
1 Answers

If I follow you correctly, you can do this with a check constraint and an array-based exclusion constraint:

create table mytable (
    id serial primary key,
    custom_barcode int,
    system_barcode int,
    check (custom_barcode <> system_barcode),
    exclude using gist((array[custom_barcode, system_barcode]) with &&)
);

The check constraint ensures that the same value cannot be given to the two columns on the same row.

The exclusion constraint builds an array containing the two codes, and ensures that there is no over row whose array overlaps the current row.

Here is a demo:

insert into mytable (custom_barcode, system_barcode) values (1, 1);
-- ERROR:  new row for relation "mytable" violates check constraint "mytable_check"

insert into mytable (custom_barcode, system_barcode) values (2, 3);
-- ok

insert into mytable (custom_barcode, system_barcode) values (4, 2);
-- ERROR:  conflicting key value violates exclusion constraint "mytable_array_excl"
-- DETAIL:  Key ((ARRAY[custom_barcode, system_barcode]))=({4,2}) conflicts with existing key ((ARRAY[custom_barcode, system_barcode]))=({2,3}).
user_106767160
Related