pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedREFERENCES constraint
10+ messages / 7 participants
[nested] [flat]
* REFERENCES constraint
@ 2001-08-08 16:02 Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-08 16:30 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-08 17:26 ` Re: REFERENCES constraint Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
2001-08-08 18:14 ` Re: REFERENCES constraint Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 4 replies; 10+ messages in thread
From: Cedar Cox @ 2001-08-08 16:02 UTC (permalink / raw)
To: pgsql-sql
Two questions (maybe they are silly..)
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)
)
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)
)
I'm guessing the answer is no, in which case I have to fall back to
writing custom PL/ functions and triggers. Just thought I'd ask anyway.
It would be nice if it were this easy though...
-Cedar
p.s. please CC my on this thread.. I'm on vacation (on this list) :0
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-08 16:29 ` Josh Berkus <josh@agliodbs.com>
2001-08-08 17:50 ` Re: REFERENCES constraint Jan Wieck <JanWieck@Yahoo.com>
3 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2001-08-08 16:29 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; pgsql-sql
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.
> 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?
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
@ 2001-08-08 17:50 ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-12 19:01 ` Re: REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2001-08-08 17:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Cedar Cox <cedarc@visionforisrael.com>; pgsql-sql
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.
>
> > 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
it'd build an expert-directory-structure (experts usually
have "alias rm='/bin/rm -rf'" in their .profile, you know).
Again, since it makes sense it is possible.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-08 17:50 ` Re: REFERENCES constraint Jan Wieck <JanWieck@Yahoo.com>
@ 2001-08-12 19:01 ` Cedar Cox <cedarc@visionforisrael.com>
2001-08-12 19:42 ` Re: REFERENCES constraint Peter Eisentraut <peter_e@gmx.net>
2001-08-13 02:06 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-13 13:43 ` Re: REFERENCES constraint agutier <alangutierrez@hotmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Cedar Cox @ 2001-08-12 19:01 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: 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..
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-08 17:50 ` Re: REFERENCES constraint Jan Wieck <JanWieck@Yahoo.com>
2001-08-12 19:01 ` Re: REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-12 19:42 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 10+ messages in thread
From: Peter Eisentraut @ 2001-08-12 19:42 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql
Cedar Cox writes:
> CREATE TABLE obj_weights (
> object_id int4 REFERENCES ( apple_objects(id) OR banana_objects(id) )
> weight float4,
> )
The way I would work this out is like this:
CREATE TABLE fruit_objects (
object_id serial PRIMARY KEY,
fruit_type text CHECK (fruit_type IN ('apple', 'banana')),
weight float4,
... other common fruit properties ...
);
CREATE TABLE apple_objects (
object_id integer REFERENCES fruit_objects,
color text,
... more apple properties ...
);
CREATE TABLE banana_objects (
object_id integer REFERENCES fruit_objects,
curvature real,
... other banana properties ...
);
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-08 17:50 ` Re: REFERENCES constraint Jan Wieck <JanWieck@Yahoo.com>
2001-08-12 19:01 ` Re: REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-13 02:06 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2001-08-13 02:06 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-sql
Cedar,
> 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?
That actually makes a *lot* more sense. In fact, I'm doing the same
thing with two tables in my database ... one called "mod_data" and
another one called "notes". The first stores modifications users and
timestamps for 6 different tables, the second stores scrolling user
notes for foure different tables. Thus mod_data should look like:
CREATE TABLE mod_data (
usq INT NOT NULL REFERENCES (clients(usq) OR candidates(usq) or
orders(usq) or ... )
entry_user INT NOT NULL
entry_date TIMESTAMP NOT NULL DEFAULT current_timestamp
mod_user INT NOT NULL
mod_date TIMESTAMP NOT NULL DEFAULT current_timestamp
)
But, as you've observed, this is quite impossible. The entire structure
of relationality has been constructed around the heirarchy and/or star
topology; there is no provision for this kind of relationship. I can
think of a number of good reasons not to attempt to implement REFERENCES
for this kind of structure.
So you can deal with it as I did:
1. Users have no ability to add or modify records in the child-of-many
table. They must push data through functions that I have defined, which
take care of creating/updating the dependant records.
2. All major tables subscribe to a single "Univeral Sequence" that
supplies unique surrogate keys for the six tables. This makes the ID
unique not only within but between the 6 tables. The one disadvantage
is that it is a postgresql-only implementation.
3. Users cannot modify this "usq". Nor can they delete records.
Thus my DB integrity is protected. In a database where user access is
less restrained, you may find that the cost of creating all the triggers
necessary to deal with user updates and deletes is a lot more than the
effort to duplicate a few tables.
An alternate approach is for you to define your own updatable views.
While quite labor-intensive, this approach takes care of quite a few
complex relationship structures by forcing the users to push their
inserts and updates through the views. Properly designed, the user
would not even realize that banana_weight and apple_weight are in the
same table and are in a different table from banana_data and apple_data.
Or, perhaps, as a very advanced user, I'm just making some very advanced
mistakes ...
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Re: REFERENCES constraint Josh Berkus <josh@agliodbs.com>
2001-08-08 17:50 ` Re: REFERENCES constraint Jan Wieck <JanWieck@Yahoo.com>
2001-08-12 19:01 ` Re: REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-13 13:43 ` agutier <alangutierrez@hotmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: alangutierrez@hotmail.com @ 2001-08-13 13:43 UTC (permalink / raw)
To: pgsql-sql
cedarc@visionforisrael.com (Cedar Cox) wrote in message news:<Pine.LNX.4.21.0108122137590.11622-100000@nanu.visionforisrael.com>...
> 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?
What about this example:
Say you want all addresses in one table. This is something that I've
wanted. And you want both employees and vendors to reference the
address entity. Using the mythical syntax in this thread:
CREATE TABLE vendor (
vendor_id integer,
name varchar(32),
PRIMARY KEY (company_id)
)
CREATE TABLE employee (
employee_id integer,
first_name varchar(32),
last_name varchar(32),
PRIMARY KEY (employee_id)
)
CREATE TABLE address (
address_id integer,
object_id REFERENCES vendor OR REFERENCES employee,
address varchar(32),
city varchar(32),
state varchar(2),
zip varchar(9),
PRIMARY KEY (address_id)
)
This is what I've done:
Create an address table, it references no one.
CREATE TABLE address (
address_id integer,
address varchar(32),
city varchar(32),
state varchar(2),
zip varchar(9),
PRIMARY KEY (address_id)
)
Assuming an employee has only one address, the employee table
references the address table.
CREATE TABLE employee (
employee_id integer,
first_name varchar(32),
last_name varchar(32),
address_id integer REFERENCES address
PRIMARY KEY (employee_id)
)
Assuming that a vendor can have more than one address:
CREATE TABLE vendor (
vendor_id integer,
name varchar(32),
PRIMARY KEY (company_id)
)
CREATE TABLE vendor_address (
vendor_id integer REFERENCS vendor,
address_id integer REFERNCES address,
PRIMARY KEY (vendor_id, address_id)
)
Just a thought
Alan Gutierrez - alangutierrez@hotmail.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-08 16:30 ` Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2001-08-08 16:30 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; pgsql-sql
Cedar,
(sorry for the double posting, folks)
> 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)
> )
You don't do this with REFERENCES ... you do it with a CHECK CONSTRAINT
(look it up in the docs).
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-08 17:26 ` Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
3 siblings, 0 replies; 10+ messages in thread
From: Grigoriy G. Vovk @ 2001-08-08 17:26 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-sql
As for me it is looking as not good normalized structure.
After normalization should not be any questions, I think...
Aug 8, 19:02 +0300, Cedar Cox wrote:
>
> Two questions (maybe they are silly..)
>
> 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)
> )
>
> 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)
> )
>
> I'm guessing the answer is no, in which case I have to fall back to
> writing custom PL/ functions and triggers. Just thought I'd ask anyway.
> It would be nice if it were this easy though...
>
> -Cedar
> p.s. please CC my on this thread.. I'm on vacation (on this list) :0
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
my best regards,
----------------
Grigoriy G. Vovk
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: REFERENCES constraint
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
@ 2001-08-08 18:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
3 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2001-08-08 18:14 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-sql
On Wed, 8 Aug 2001, Cedar Cox wrote:
>
> Two questions (maybe they are silly..)
>
> 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)
> )
Yes, you should be able to do this, but it won't do what you want, it'll
make it be required in both tables. You'll need to do this one by hand.
> 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)
> )
Yes, using your first syntax that should work.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-08-13 13:43 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-08 16:02 REFERENCES constraint Cedar Cox <cedarc@visionforisrael.com>
2001-08-08 16:29 ` Josh Berkus <josh@agliodbs.com>
2001-08-08 17:50 ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-12 19:01 ` Cedar Cox <cedarc@visionforisrael.com>
2001-08-12 19:42 ` Peter Eisentraut <peter_e@gmx.net>
2001-08-13 02:06 ` Josh Berkus <josh@agliodbs.com>
2001-08-13 13:43 ` agutier <alangutierrez@hotmail.com>
2001-08-08 16:30 ` Josh Berkus <josh@agliodbs.com>
2001-08-08 17:26 ` Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
2001-08-08 18:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox