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_pkeyPRIMARY KEY (agreement_id), CONSTRAINT agreements_subject_id_fkey FOREIGN KEY (subject_id)
REFERENCESsubjects (subject_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT
check_agreements_onlyone_approvedCHECK
(check_agreements_onlyone_approved(subject_id))
);
CREATE FUNCTION check_agreements_onlyone_approved(a_subject_id integer)
RETURNS boolean AS
$$
SELECTCASE COUNT(agreement_id) WHEN 0 THEN true WHEN 1 THEN true ELSE falseEND 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
