• CHECK constraint to limit certain rows

    From Robin Koch@21:1/5 to All on Tue Aug 23 06:12:17 2016

    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
    singular boolean;
    cnt integer;
    stmt text;

    -- 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;
    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?

    Please note:
    (1) This is a clone of my question here: http://dba.stackexchange.com/questions/147463/check-constraint-to-limit-certain-rows
    (2) I just use google groups, because I'm at work. :-)


    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)