From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: edouard.boucher@free.fr
Cc: pgsql-sql@postgresql.org
Subject: Re: check with select
Date: Thu, 15 May 2003 07:49:17 -0700 (PDT)
Message-ID: <20030515074312.V60859-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <20030515122001.GA7851@comploteur.gibbonie.creolie>
On Thu, 15 May 2003 edouard.boucher@free.fr wrote:
> I have a table referancing services of a company :
> CREATE TABLE Services (
> id INTEGER NOT NULL PRIMARY KEY,
> nom VARCHAR(30) NOT NULL -- name of the service
> );
>
> each of them can have many workers and many boss
> but at least 1 boss
>
> So i have a table referancing workers and boss :
>
> CREATE TABLE EmployesDsServices (
> service INTEGER REFERENCES Services (id)
> ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
> employe INTEGER REFERENCES Employes (id)
> ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
> niveau CHAR(1), -- R -> boss, S -> worker
> CONSTRAINT niveau_valide CHECK (NIVEAU IN ('R','S')),
> PRIMARY KEY (service, employe)
> );
>
> to check that at any time a service always have at least one boss,
> i would like :
>
> CREATE ASSERTION chaque_service_a_au_moins_un_responsable CHECK
> (NOT EXISTS (SELECT id FROM Services LEFT OUTER JOIN
> (SELECT * FROM employesdsservices WHERE niveau='R') AS resp ON
> id=service GROUP BY id HAVING COUNT(niveau)<1));
>
> the problem is that this is not valid in postgreSQL,
Noone's done assertions yet, and PostgreSQL also doesn't support
subqueries in check constraints at this point either AFAIK.
> so i tryed to use a trigger, but it seems that it is not possible to
> make a trigger deferrable (as the 2 tables are mutualy dependent).
It should be possible, but you have to use a not meant for general use
(and barely documented) CREATE CONSTRAINT TRIGGER, or you may be able to
twiddle the entries for a normal trigger in pg_trigger after the fact, but
I haven't tried the latter. CREATE CONSTRAINT TRIGGER was effectively
mean for references constraints in dumps before they started being dumped
as alter statements.
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: sszabo@megazone23.bigpanda.com, edouard.boucher@free.fr
Subject: Re: check with select
In-Reply-To: <20030515074312.V60859-100000@megazone23.bigpanda.com>
* 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