Thread: check with select

Hi,

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
DEFERRABLEINITIALLY DEFERRED,      employe INTEGER REFERENCES Employes (id)       ON DELETE CASCADE DEFERRABLE
INITIALLYDEFERRED,      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,
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).

i would like something i can use like this :

begin;
insert into services ...
insert into employesdsservices ...
end;

is there a way to do this, a check after each insert, update, delete in
both tables?

i know my constraint is anyway very slow, but this is for a scolar
project about theory and performance realy dont matters.

thank you for help

Edouard Boucher

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.