Re: CHECK constraint

paul butler wrote:
> Date sent: Thu, 13 Mar 2003 14:35:40 +0000
> From: Ben Clewett <B(dot)Clewett(at)roadrunner(dot)uk(dot)com>
> Copies to: pgsql-novice(at)postgresql(dot)org
> Subject: [NOVICE] CHECK constraint
>
> Ben,
> Would foreign keys not be the simplest solution?

Not in my case unfortunatelly. I need a CHECK on a subset of referenced
values:

(in this case where 'live = true', mine's a bit more complex...)

CREATE TABLE foo (
id int4 NOT NULL CHECK (
id IN ( SELECT id FROM bar WHERE live = true ) ),
FOREIGN KEY (id) REFERENCES bar (id)
)

I believe this is not (yet) possible in our favorite SQL, although part
of SQL1999. Is this therefore only available through a TRIGGER, or
maybe there is a more elegent method?

Like a FK to a VIEW:

CREATE VIEW v_bar
SELECT * from BAR WHERE live=true

Then my table def becomes:

CREATE TABLE foo (
id int4 NOT NULL,
FOREIGN KEY (id) REFERENCES v_bar (id)
)

Is this possible?? Should I cut-and-run here and do the coding in
application space?

Ben

>
> CREATE TABLE foo(
>
> id int4 NOT NULL,
> FOREIGN KEY (id) REFERENCES bar(id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
>
> )
> In SQL standards, I belive a SELECT query is valid in a check constraint:
>
> CREATE TABLE foo (
> a INT CHECK ( a IN ( SELECT b FROM bar WHERE .... ) )
> }
>
>
>
>>However, this seems not to be the case (yet) in PostgreSQL.
>>
>>Should I do this with Triggers instead? Are there any other elegent
>>methods of doing the same?
>
>
>
> Ben
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>