Enforcing that all columns have different values

user_5214911 0
Viewed 37

A particular val# should be in no more than one column. Multiple columns can be empty though. (I don't want to use NULL instead of empty.)

CREATE TYPE vals AS ENUM ('val1', 'val2', 'val3', 'val4', 'val5', ... 'empty');
CREATE TABLE some_table
( ...
  column1 vals NOT NULL,
  column2 vals NOT NULL,
  column3 vals NOT NULL,
  CONSTRAINT some_table_column_vals_check CHECK (???)
... );

Valid combinations e.g.:

column1: val1
column2: val2
column3: val4

column1: val1
column2: empty
column3: empty

Invalid combinations e.g.:

column1: val1
column2: val3
column3: val3

column1: val2
column2: empty
column3: val2

Is there a neat way to do this with a (preferably not too long) constraint, or should I write a trigger function for that?

database-trigger check-constraints postgresql sql
1 Answers

One method is a rather painful case expression in a check constraint:

alter table some_table add constraint chk_some_tablefields
    check ( (val1 not in (val2, val3) or val1 = 'empty') and
            (val2 not in (val3) or val2 = 'empty')
          );

However, I would caution you about your data structure. You should probably have a junction/association table with one row per val and some_table id. Or you might want to just store the values in an array, if you want a variable number of them.

user_11440350
Related