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?