From: "Greg Patnude" <gpatnude@hotmail.com>
Subject: Re: Unique Constraint with foreign Key
Date: Wed, 11 Feb 2004 15:33:50 -0800
Organization: Hub.Org Networking Services
Lines: 51
Message-ID: <c0ee6t$j4v$1@news.hub.org>
References: <64366.138.88.163.115.1076432616.squirrel@physics.gmu.edu>
	<20040210201521.GA13737@wolff.to>
To: pgsql-sql@postgresql.org

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:

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),

);


-- 
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Bruno Wolff III" <bruno@wolff.to> wrote in message
news:20040210201521.GA13737@wolff.to...
> On Tue, Feb 10, 2004 at 12:03:36 -0500,
>   mohan@physics.gmu.edu wrote:
> > Hi all i am using postgres and torque
> > I have a column x in Table A of type varchar and NOT NULL. I want to
> > impose a unique key constraint on that table and column y is the primary
> > key.
> > I have another Table B with column x. can i make this column x as a
> > foreign key on column x in table A.
>
> Yes. The reference will need to specify column x, since the primary key
> column (y) will be the default.
>
> > or Can i have varchar types  of size 50 as primary keys in Postgres.
>
> Yes. But unless the 50 character limit comes from a business rule, you
> might want to use the type 'text'.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>