Re: foreign key to multiple tables depending on another column's value

Rodrigo Rosenfeld Rosas <rr.rosas@xxxxxxxxx> · Wed, 29 May 2013 13:14:00 -0300



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

    
          On Wed, May 29, 2013 at 9:58 AM,
            Rodrigo Rosenfeld Rosas <rr.rosas@xxxxxxxxx> 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.