Re: REFERENCES constraint
От | Cedar Cox |
---|---|
Тема | Re: REFERENCES constraint |
Дата | |
Msg-id | Pine.LNX.4.21.0108122137590.11622-100000@nanu.visionforisrael.com обсуждение исходный текст |
Ответ на | Re: REFERENCES constraint (Jan Wieck <JanWieck@Yahoo.com>) |
Ответы |
Re: REFERENCES constraint
(Peter Eisentraut <peter_e@gmx.net>)
Re: REFERENCES constraint ("Josh Berkus" <josh@agliodbs.com>) |
Список | pgsql-sql |
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..
В списке pgsql-sql по дате отправления: