[SQL] Re: [GENERAL] foreign key to multiple tables depending on another column's value

[Rodrigo Rosenfeld Rosas]

Em 29-05-2013 12:51, Vick Khera escreveu:

On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas 
<rr.ro...@gmail.com <mailto:rr.ro...@gmail.com>> wrote:

    I know I could use a trigger, or some check constraint maybe, to
    ensure the field exists upon insert (or update), but I can't
    ensure the database will become inconsistent in case I remove a
    mapped field from the other schema.

    Now I can finally explain my question: is it possible that I set
    some sort of foreign key whose referenced table and column would
    depend on the value of another column?


The FK tests are basically triggers, but highly optimized.

That said, the way they enforce the integrity is by having a trigger 
on both tables. So for your custom need here, you would want to put a 
trigger on the referenced table to disallow deleting a value that is 
still referenced, or do whatever appropriate action upon delete/update 
your application needs.


Ok, thanks. I just wanted to be sure there wasn't some hidden feature of 
PostgreSQL I wasn't aware of yet...

You know, I'm always learning something new on PG, so it worths trying 
to ask first ;)

Cheers,
Rodrigo.