Re: REFERENCES constraint

On Wed, 8 Aug 2001, Jan Wieck wrote:
> Josh Berkus wrote:
> > Cedar,
> >
> > > 1. Can a column reference more than one table?  (This assumes you use
> > > a
> > > single sequence to generate the IDs for both "tbla" and "tblb".  I
> > > guess
> > > you would also have the problem of enforcing a unique index.  Say
> > > what?!
> > > A unique index across multiple tables.. absurd :)  eg..
> > >
> > >   CREATE TABLE blah (
> > >     id int4,
> > >     f_id int4 REFERENCES tbla (id) REFERENCES tblb (id)
> > >   )
> >
> > I'd reccomend, instead, having blah reference tbla and tbla reference
> > tblb.  It'd have the same effect, without forcing you to monkey around
> > with custom triggers.
> 
>     Nobody  said  that  primary  keys  are  limited to the serial
>     datatype. So in case that tbla and tblb could have  different
>     sets  of keys with a possible intersection, and further given
>     that blah.f_id shall  be  limited  to  values  both  have  in
>     common, there's no other way than having multiple foreign key
>     constraints on that one column.
> 
>     Thus, it is possible. I'm not sure if  the  above  syntax  is
>     supported,  but  at  least you can put table level CONSTRAINT
>     clauses into the statement and/or add the  constraints  later
>     with ALTER TABLE.

I guess I wasn't clear.  Let me try to explain again:
 CREATE TABLE obj_weights (   object_id int4 REFERENCES ( apple_objects(id) OR banana_objects(id) )   weight float4, )

"apple_objects" doesn't necessarily have anything to do with
"banana_objects".  Ok, don't ask why you would want to store weights of
apples and bananas in the same table.. (and if you know, please tell me).  
This is all actually for someone else's database that I just picked up.  
They did something like this.. single sequence for the whole database,
multiple object tables, and a table(s) referencing objects that could come
from any of those tables.  Maybe this is just bad design.  Thoughts
anyone?

> > > 2. Can a column reference another column in the same table?  eg..
> > >
> > >   CREATE TABLE bloo (
> > >     id int4,
> > >     p_id int4 REFERENCES bloo (id)
> > >   -- or
> > >   --p_id int4 REFERENCES (id)
> > >   )
> >
> > Er ... why would you want to?
> 
>     To  build  a  tree  structure  of nodes. Root nodes have p_id
>     (meaning parent-id I guess) set to NULL, all others must have
>     an  existing node as parent.  Together with ON DELETE CASCADE

Exactly :)  Having already done this with custom triggers, I now want to
know if there is an "easy" way...

-Cedar
p.s. please CC me on this thread..