Re: Unique Constraint with foreign Key

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(at)wolff(dot)to> wrote in message
news:20040210201521(dot)GA13737(at)wolff(dot)to(dot)(dot)(dot)
> On Tue, Feb 10, 2004 at 12:03:36 -0500,
> mohan(at)physics(dot)gmu(dot)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
>