X-Received: by 10.237.35.174 with SMTP id j43mr20361394qtc.29.1471957937402; Tue, 23 Aug 2016 06:12:17 -0700 (PDT)
X-Received: by 10.157.44.69 with SMTP id f63mr1406227otb.2.1471957937344; Tue, 23 Aug 2016 06:12:17 -0700 (PDT)
Path: csiph.com!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!j37no10164150qta.0!news-out.google.com!d130ni44318ith.0!nntp.google.com!f6no12300489ith.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.postgresql
Date: Tue, 23 Aug 2016 06:12:17 -0700 (PDT)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=165.225.80.164; posting-account=wM6biAoAAACpU28USYnV6QECDxUBif6M
NNTP-Posting-Host: 165.225.80.164
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <22cfc7fa-e121-4095-8b9f-20b359e3f29d@googlegroups.com>
Subject: CHECK constraint to limit certain rows
From: Robin Koch <rkoch83@googlemail.com>
Injection-Date: Tue, 23 Aug 2016 13:12:17 +0000
Content-Type: text/plain; charset=UTF-8
X-Received-Bytes: 3315
X-Received-Body-CRC: 2387634154
Xref: csiph.com comp.databases.postgresql:719

Moin

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?


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. :-)

Robin