Re: CHECK constraint

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)
>