paul butler wrote:
> Date sent: Thu, 13 Mar 2003 14:35:40 +0000
> From: Ben Clewett <B.Clewett@roadrunner.uk.com>
> Copies to: pgsql-novice@postgresql.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@postgresql.org)
>
