Thread: Unique Constraint with foreign Key
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. or Can i have varchar types of size 50 as primary keys in Postgres. please let me know --Mohan
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'.
unsubscribe
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
>
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 #
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@adelphia.net
Website: http://www.left-center.com
-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Tuesday, February 17, 2004 6:42 AM
To: Greg Patnude
Cc: pgsql-sql@postgresql.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@Yahoo.com #
> > 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'. And if that limit does come from a business rule you might want to think twice whether using columns constrained by business rules are good candidates for primary keys. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346