From: Jan Wieck <JanWieck@Yahoo.com>
To: Greg Patnude <gpatnude@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Unique Constraint with foreign Key
Date: Tue, 17 Feb 2004 09:41:45 -0500
Message-ID: <40322829.3050603@Yahoo.com> (raw)
In-Reply-To: <c0ee6t$j4v$1@news.hub.org>
References: <64366.138.88.163.115.1076432616.squirrel@physics.gmu.edu>
<20040210201521.GA13737@wolff.to>
<c0ee6t$j4v$1@news.hub.org>
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@Yahoo.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: JanWieck@Yahoo.com, gpatnude@hotmail.com
Subject: Re: Unique Constraint with foreign Key
In-Reply-To: <40322829.3050603@Yahoo.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