CHECK constraint to limit certain rows

Question

I have the task to create an EAV table with some constraints.

Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint).

On this table are some constraints to be realized, one of which is that some object/attribute-combinations must be unique, while others don't.

Example: A photo can have multiple persons in it but only have one person who took it.

So I wrote a function to limit object/value-combination to max. 1, if a certain boolean is set for that attribute. (As I said, the attributes come as foreign keys from a different table.)

-- Create function (for use in CHECK constraint) that checks if
-- an objects get two value on the same attribute,
-- unless it's explicitly allowed in attributes table
CREATE OR REPLACE FUNCTION isNoDisallowedDoublette(_object text, _attribute text)
RETURNS boolean AS
$BODY$
  DECLARE
    singular    boolean;
    cnt         integer;
    stmt        text;
  BEGIN

    -- determine if attribute needs to be unique for object
    EXECUTE 'SELECT singular FROM attributes'
      ||  'WHERE attribute = '''
      ||  _attribute
      ||  ''';'
    INTO singular;

    -- if there are multiple values allowed, continue
    -- Note: if attributes.singular is not set,
    -- allow only one value to avoid conflicts
    IF singular IS FALSE THEN
      RETURN true;
    END IF;

    -- count entries identical to _row
    EXECUTE 'SELECT count(*) FROM objects '
        ||  'WHERE object = ''' 
        ||  _object
        ||  ''' AND attribute = '''
        ||  _attribute
        || ''';'
    INTO cnt;

    RETURN cnt = 0;
  END
$BODY$
LANGUAGE plpgsql;

This functions checks, if a particular object/attribute-combination is not yet present in the table (cnt = 0).

This works fine against inserting unwanted doublets.

The problem however is, that it doesn't allow updating such row. The updated row seems to be considered a doublet.

Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Robin

Answer 1

The problem however is, that it doesn't allow updating such row. The updated row seems to be considered a doublet.

That's one of the problems.

Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Yes, use "on insert" and "on update" triggers, and yes "check" constraints are unsuited.

you'll also need an "on update" trigger on "attribute" so that changes of singular are verified against the existing data.

A different approach would be to have partial unique indices generated by a trigger that tracks inserts, deletes, and updates on attribute.

Eg. if you have a attribute day_of_birth which is singular you would do

CREATE UNIQUE INDEX "object_day_of_birth_unique" ON objects(object) WHERE attribute='day_of_birth';

This leverages something that the database engine is good at, and so will give a slight performance boost, but as you're using EAV I suspect perfromance is not important.

when writing such a trigger be sure to use quote_literal and quote_ident where apropriate in execute expressions.