Обсуждение: Foreign keys?
There doesn't seem to be much documentation on foreign keys and how to
use them properly.
I am setting up a DB with two tables: users, and a table of objects
where are always owned by users. I want to make sure that there's no
way to have an object which isn't owned. Let's say these are the
tables:
CREATE TABLE user (
number INT4,
name VARCHAR(400)
);
and
CREATE TABLE object (
owner INT4,
description VARCHAR(200)
);
The constraint is that there should never be a row in the object table
where the owner column has a number which doesn't have a corresponding
owner in the user table.
I'm sure I can do something with foreign keys to implement this
constraint, but I can't figure it out.
Thanks
The simple answer is as easy as:
CREATE TABLE user (
number INT4 PRIMARY KEY,
name VARCHAR(400)
);
CREATE TABLE object (
owner INT4 REFERENCES user NOT NULL,
description VARCHAR(200)
);
This will guarantee that object.owner will always be
one of the user.number values, and that you will not
be able to delete users that still have objects. Both
of these actions will raise exceptions.
However, if you are going to do a lot of joins on your
user table along the lines of:
SELECT user.name, object.description FROM user, object
WHERE user.number = object.owner;
Then you might be better off simplifying just a bit to
give you something like:
CREATE TABLE user (
name VARCHAR(400) PRIMARY KEY
);
CREATE TABLE object (
owner VARCHAR(400) REFERENCES user NOT NULL,
description VARCHAR(200)
);
That would save you having to join the table to find
the user.name at the expense of using more hard drive
space.
For more information see Bruce Momjian's excellent
book:
http://www.ca.postgresql.org/docs/aw_pgsql_book/node131.html
And if you like it consider buying a copy. It's well
worth it.
Jason
--- "Dr. Evil" <drevil@sidereal.kz> wrote:
>
> There doesn't seem to be much documentation on
> foreign keys and how to
> use them properly.
>
> I am setting up a DB with two tables: users, and a
> table of objects
> where are always owned by users. I want to make
> sure that there's no
> way to have an object which isn't owned. Let's say
> these are the
> tables:
>
> CREATE TABLE user (
> number INT4,
> name VARCHAR(400)
> );
>
> and
>
> CREATE TABLE object (
> owner INT4,
> description VARCHAR(200)
> );
>
> The constraint is that there should never be a row
> in the object table
> where the owner column has a number which doesn't
> have a corresponding
> owner in the user table.
>
> I'm sure I can do something with foreign keys to
> implement this
> constraint, but I can't figure it out.
>
> Thanks
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
On 13 Jul 2001, Dr. Evil wrote: > > There doesn't seem to be much documentation on foreign keys and how to > use them properly. > > I am setting up a DB with two tables: users, and a table of objects > where are always owned by users. I want to make sure that there's no > way to have an object which isn't owned. Let's say these are the > tables: > > The constraint is that there should never be a row in the object table > where the owner column has a number which doesn't have a corresponding > owner in the user table. > > I'm sure I can do something with foreign keys to implement this > constraint, but I can't figure it out. You need to define user.number as unique and add the constraint, something like: create table user ( number int4 unique, name varchar(400) ); create table object ( owner int4 references user(number), description varchar(200) ) However, there's different options based on what you want it to do in the case you modify or delete rows out of user.
From: "Jason Earl" <jdearl@yahoo.com> > However, if you are going to do a lot of joins on your > user table along the lines of: > > SELECT user.name, object.description FROM user, object > WHERE user.number = object.owner; > > Then you might be better off simplifying just a bit to > give you something like: > > CREATE TABLE user ( > name VARCHAR(400) PRIMARY KEY > ); > > CREATE TABLE object ( > owner VARCHAR(400) REFERENCES user NOT NULL, > description VARCHAR(200) > ); > > That would save you having to join the table to find > the user.name at the expense of using more hard drive > space. I'm curious - are you speaking from a performance viewpoint here, or just about simplifying queries (in which case I'd just slap a view on top)? - Richard Huxton
It was a little bit late when I wrote that, and so I probably should have been a little more specific. I don't know if you would notice a performance difference between the joined tables query and and the non-joined version for such simple tables. I might have to spend a bit of time today loading a test database with sufficient data to test it, because now I am curious. However, I know that if your tables are more involved than the trivial ones that I included that it can make a big difference. This is especially true if you want to join a table to several lookup tables. In those cases it is a serious performance win to have the data in the master table and simply use the lookup tables to guarantee that valid data is entered. By the time you have a query that looks like this: SELECT users.name, states.name, institutions.name, divisions.name, trucks.id from users, states, institutions, divisions, trucks WHERE users.state = states.id AND users.institution = institutions.id AND users.division = divisions.id AND users.truck = trucks.id AND users.id = 'MYID'; PostgreSQL is going to wish that you had put more of that information in the users table. A view might make the query easier to type, but it won't undo the performance penalty of multiple joins. At least that is how I understand it. I might be wrong, however, I never have pretended to be a SQL guru, but I certainly noticed a performance difference when I switched from a table with multiple joins to one with more of the information directly in the table (it still referenced primary keys in another table, they just were varchar primary keys and not ints). Jason --- Richard Huxton <dev@archonet.com> wrote: > From: "Jason Earl" <jdearl@yahoo.com> > > > However, if you are going to do a lot of joins on > your > > user table along the lines of: > > > > SELECT user.name, object.description FROM user, > object > > WHERE user.number = object.owner; > > > > Then you might be better off simplifying just a > bit to > > give you something like: > > > > CREATE TABLE user ( > > name VARCHAR(400) PRIMARY KEY > > ); > > > > CREATE TABLE object ( > > owner VARCHAR(400) REFERENCES user NOT > NULL, > > description VARCHAR(200) > > ); > > > > That would save you having to join the table to > find > > the user.name at the expense of using more hard > drive > > space. > > I'm curious - are you speaking from a performance > viewpoint here, or just > about simplifying queries (in which case I'd just > slap a view on top)? > > - Richard Huxton > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/