From: alangutierrez@hotmail.com
To: pgsql-sql@postgresql.org
Subject: Re: REFERENCES constraint
Date: 13 Aug 2001 06:43:18 -0700
Message-ID: <a4e0e596.0108130543.6fe132a8@posting.google.com> (raw)
References: <Pine.LNX.4.21.0108122137590.11622-100000@nanu.visionforisrael.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alangutierrez@hotmail.com
Subject: Re: REFERENCES constraint
In-Reply-To: <a4e0e596.0108130543.6fe132a8@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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