From: | "Greg Patnude" <gpatnude(at)hotmail(dot)com> |
---|---|
To: | "'Jan Wieck'" <JanWieck(at)Yahoo(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Unique Constraint with foreign Key |
Date: | 2004-02-17 17:22:44 |
Message-ID: | 000301c3f57a$a7d95800$6401a8c0@dpanel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
You've got it wrong when you reference the data column (a.x) -- your
foreign key should reference the primary key in the referenced table
(a.y)...
Besides, in your table A -- 1, 99 2, 99 violates your unique constraint
on column 'X' -- it would never happen...
What I suggested is like this:
create table a (
y integer not null primary key default nextval('nexta_seq'),
x varchar not null UNIQUE
);
create table b (
z integer not null PRIMARY KEY default nextval('nextbz_seq'),
x integer NOT NULL REFERENCES a(y),
);
Table A would have
1, 99
2, 99
..
99,99
and table B would have
1, 1
1, 2
..
1, 99
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID. 83835
(208) 762-0762
Send replies to: gpatnude(at)adelphia(dot)net
Website: http://www.left-center.com
-----Original Message-----
From: Jan Wieck [mailto:JanWieck(at)Yahoo(dot)com]
Sent: Tuesday, February 17, 2004 6:42 AM
To: Greg Patnude
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Unique Constraint with foreign Key
Greg Patnude wrote:
> Pleas also note that the referenced column in the foreign table either
needs
> to be the PRIMARY KEY or have a unique constraint on it or maybe it
just
> requires an index on it -- I'm not sure but I discovered that if the
column
> in the foreign table (containing the REFERENCED key...) is NOT the
primary
> key column -- the REFERENCES a(x) will faill unless a.x is specified
as
> 'UNIQUE' -- as in the following example:
This is according to the SQL specification, which doesn't like doubts.
Imagine rows (1, 99), (2, 99) in table a and row (3, 99) in table b.
Which of the a-rows is now referenced and am I allowed to delete the
other? There are good arguments either way, but if you require a UNIQUE
on a.x, then this question will never come up.
Jan
>
> create table a (
> y integer not null primary key default nextval('nexta_seq'),
> x varchar not null UNIQUE
>
> );
>
> create table b (
>
> z integer not null PRIMARY KEY default nextval('nextbz_seq'),
> x varchar NOT NULL REFERENCES a(x),
>
> );
>
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #
From | Date | Subject | |
---|---|---|---|
Next Message | Theodore Petrosky | 2004-02-17 17:41:13 | need help with os x objects into a db |
Previous Message | Jeremy Smith | 2004-02-17 17:00:33 | Re: bytea or blobs? |