From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-sql@postgresql.org
Subject: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
Date: Mon, 19 Apr 2010 11:49:32 +0200
Message-ID: <4BCC272C.3020505@megafon.hr> (raw)
The 'data integrity' rule for database I'm designing says that any
subject we're tracking (persons, companies, whatever) is assigned an
agreement that can be in several states: 'Approved', 'Unapproved' or
'Obsolete'. One subject can have only one (or none) 'Approved' or
'Unapproved' agreement, and as many (or none) 'Obsolete' agreements.
I was thinking on employing the CHECK constraint on agreements table
that would check that there is only one 'Approved' state per subject.
My (simplified) schema looks like this:
CREATE TYPE enum_agreement_state AS ENUM
('unapproved',
'approved',
'obsolete');
CREATE TABLE subjects
(
subject_id serial NOT NULL,
subject_name character varying NOT NULL,
CONSTRAINT subjects_pkey PRIMARY KEY (subject_id)
);
CREATE TABLE agreements
(
agreement_id serial NOT NULL,
subject_id integer NOT NULL,
agreement_state enum_agreement_state NOT NULL,
CONSTRAINT agreements_pkey PRIMARY KEY (agreement_id),
CONSTRAINT agreements_subject_id_fkey FOREIGN KEY (subject_id)
REFERENCES subjects (subject_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT check_agreements_onlyone_approved CHECK
(check_agreements_onlyone_approved(subject_id))
);
CREATE FUNCTION check_agreements_onlyone_approved(a_subject_id integer)
RETURNS boolean AS
$$
SELECT
CASE COUNT(agreement_id)
WHEN 0 THEN true
WHEN 1 THEN true
ELSE false
END FROM agreements WHERE subject_id = $1 AND agreement_state = 'approved';
$$ LANGUAGE 'sql';
Now, the above does not work because CHECK function is fired BEFORE
actuall data modification takes place so I can end up with two rows with
'approved' state for particular subject_id. If I change the CASE...WHEN
conditions so that function returns TRUE only when there is 0 rows for
the state = 'approved' then I have problems with UPDATEing:
UPDATE agreements SET agreement_state = 'obsolete' where subject_id =
<whatever> AND agreement_state = 'approved'
That update will fail because the CHECK function is fired before the
actuall update, and there is allready a row with state = 'approved' in
the table.
Now, I know I could use triggers to achieve desired functionality but I
try to use triggers as seldom as I can. Often ETL scripts disable
triggers so I could end up with data integrity broken.
The 'proper' way to do this (as suggested by earlier posts on this
mailing list) is to use partial UNIQUE indexes, but I have problem with
that too: indexes are not part of DDL (no matter that primary key
constraints and/or unique constraints use indexes to employ those
constraints), and as far as I know there is no 'partial unique
constraint' in SQL?
Does anyone has better suggestion on how to employ the data-integrity
rules I have?
And, wouldn't it be better to have CHECK constraints check the data
AFTER data-modification? I also found no reference on using CHECK
constraints with user-defined functions on postgres manual - there
should be a mention of the way the CHECK constraint works - that is,
function referenced by CHECK constraint is fired BEFORE the actual data
modification occur. The error message is also misleading, for instance,
when I run the before mentioned UPDATE:
constraint_check=# update agreements set agreement_state = 'obsolete'
where subject_id = 1 and agreement_state = 'approved';
ERROR: new row for relation "agreements" violates check constraint
"check_agreements_onlyone_approved"
Mario
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@megafon.hr
Subject: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
In-Reply-To: <4BCC272C.3020505@megafon.hr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox