Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query

Query

From
"Pedro Igor"
Date:
Regards .....
 
  I hope someone can help me in this query.
  Have a field in a table that needs to check if another table has the value that is being inserted.
  Ex:
    table A
      - id int constraint pkey_id primary key,
    
   
    table B
      - id int constraint fkey_A_B references A,
 
    Here comes my doubt:
 
    table C
      - id int constraint fkey_A_C references A check (if exists B.id = C.id)
 
    How can i build this expression so, when I insert a tupple in table C the field will check in the table A(ok, because is a foreign key) and also in table B ....
    I have tried : check (select count(b.id) from B b where b.id = id) <> 0) .... but doesn´t work ......
    I can use trigger here, but i don´t know if is the best solution .....
     
   Thanks,
 
        Pedro Igor
  

Re: Query

From
Stephan Szabo
Date:
On Fri, 3 Jan 2003, Pedro Igor wrote:

> Regards .....
>
>   I hope someone can help me in this query.
>   Have a field in a table that needs to check if another table has the value that is being inserted.
>   Ex:
>     table A
>       - id int constraint pkey_id primary key,
>
>
>     table B
>       - id int constraint fkey_A_B references A,
>
>     Here comes my doubt:
>
>     table C
>       - id int constraint fkey_A_C references A check (if exists B.id
> = C.id)
>
>     How can i build this expression so, when I insert a tupple in
> table C the field will check in the table A(ok, because is a foreign
> key) and also in table B ....

If you only want insert(/update) time checks (ie that deleting from B
doesn't violate) then a trigger should work.  I think a check constraint
to a function that does the work will work as well.  Subsellects in check
constraints are a nasty problem that hasn't been solved yet (since those
do imply the inverse constraint).

Of course if you want two foreign keys you should be able to do that as
well.