PostgreSQL – Constraint to Allow Only Unique Value in a Group

postgresql

I have a table in PostgreSQL 12 with columns "sample", "group", "value". There can be multiple samples in a group. Is it possible to implement a constraint so that each group can only have one distinct value?

EDIT:
This is for a rather complex materialized view, where I would like to make sure that there are no samples in a group that has a value that differ from the other samples. In that sense "value" should be a property of the group in this specific context, but that isn't always the case in the database. The use case is that samples are biological samples taken from different tissues ("value") and groups are e.g. "treated" and "control", and I would like to make sure that there aren't a mix of tissues within one group.

This should be ok:
('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value1'), 'sample3', 'group2', 'value2'). Here there are two samples in group 1, but they both have the same value.

This should not be ok:
('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value2'), 'sample3', 'group2', 'value2')

This should fail because the two samples in group 1 have different values.

I think some of the confusion came from that I used "unique" in the original post when I was talking about distinct values, but this has nothing to do with UNIQUE. For this to work a CHECK constraint for a row being added would depend on several other existing rows. I don't know if that works? Or if there should be a trigger maybe? I guess there could be an issue with race conditions on bulk inserts?

Best Answer

CHECK constraints cannot use subqueries, so you have to use a trigger:

CREATE FUNCTION check_one_value_per_group() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT * FROM my_table
               WHERE "group" = NEW."group"
                 AND value <> NEW.value;
              ) THEN
        RAISE EXCEPTION 'different values in group %', NEW."group";
    END IF;
    RETURN NEW;
END;
$$;

CREATE CONSTRAINT TRIGGER one_value_per_group
AFTER INSERT OR UPDATE OF "group", value ON my_table
FOR EACH ROW
EXECUTE FUNCTION check_one_value_per_group();

Related Solutions

PostgreSQL – How to Enforce an Exactly-One-Per-Group Constraint

The problem is similar to this one: How to have a one-to-many relationship with a privileged child?

The "at most one per group" part of the constraint can be solved with a partial index:

CREATE UNIQUE INDEX is_FavoriteChild
  ON x (yid)
  WHERE is_principal  ;

Another way to solve the problem is by removing the is_principal column and add a 3rd table. This doesn't solve the "exactly one" either:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y (yid),
    UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
    xid INTEGER NOT NULL,
    yid INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (yid, xid) REFERENCES x (yid, xid) 
);

If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).

For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.

(Editing the answer for the additional detail that not all values of y.yid have to appear in table x. One table is added):

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
    yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
    --- **no other columns**
    principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y_x (yid)
                         DEFERRABLE INITIALLY DEFERRED,
    UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
   FOREIGN KEY (yid, principal_xid)
   REFERENCES x (yid, xid)
   DEFERRABLE INITIALLY DEFERRED;

PostgreSQL – Why Composite Foreign Keys Need a Separate Unique Constraint

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN KEY that REFERENCES (a2, a1) unless there is a unique constraint on that (a2, a1) that essentially is redundant.

It wouldn't be terrifically difficult to add this as a feature:

When there is a UNIQUE constraint on (a), then any (a, b, c, ..., z) or (b,c, ...a, ...z) combination is also guaranteed UNIQUE.

or the generalization:

When there is a UNIQUE constraint on (a1, a2, ..., aN), then any (a1, a2, ..., aN, b1, b2, ..., bM) combination or any rearrangement is also guaranteed UNIQUE.

It seems that it hasn't been asked or it hasn't been considered high enough priority to be implemented.

You can always make a request - in the respective channel - for the feature to be implemented. Or even implement it yourself, if the DBMS is open source, like Postgres.

Best Answer

Related Solutions

PostgreSQL – How to Enforce an Exactly-One-Per-Group Constraint

PostgreSQL – Why Composite Foreign Keys Need a Separate Unique Constraint

Related Question