pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique Constraint with foreign Key
6+ messages / 5 participants
[nested] [flat]
* Unique Constraint with foreign Key
@ 2004-02-10 17:03 mohan@physics.gmu.edu
0 siblings, 1 reply; 6+ messages in thread
From: mohan@physics.gmu.edu @ 2004-02-10 17:03 UTC (permalink / raw)
To: pgsql-sql; +Cc: torque-user@db.apache.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unique Constraint with foreign Key
@ 2004-02-10 20:15 Bruno Wolff III <bruno@wolff.to>
parent: mohan@physics.gmu.edu
0 siblings, 2 replies; 6+ messages in thread
From: Bruno Wolff III @ 2004-02-10 20:15 UTC (permalink / raw)
To: mohan@physics.gmu.edu; +Cc: pgsql-sql; torque-user@db.apache.org
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'.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unique Constraint with foreign Key
@ 2004-02-11 23:33 Greg Patnude <gpatnude@hotmail.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 6+ messages in thread
From: Greg Patnude @ 2004-02-11 23:33 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unique Constraint with foreign Key
@ 2004-02-17 14:41 Jan Wieck <JanWieck@Yahoo.com>
parent: Greg Patnude <gpatnude@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jan Wieck @ 2004-02-17 14:41 UTC (permalink / raw)
To: Greg Patnude <gpatnude@hotmail.com>; +Cc: pgsql-sql
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 #
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unique Constraint with foreign Key
@ 2004-02-17 17:22 Greg Patnude <gpatnude@hotmail.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Patnude @ 2004-02-17 17:22 UTC (permalink / raw)
To: 'Jan Wieck' <JanWieck@Yahoo.com>; +Cc: 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@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 #
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unique Constraint with foreign Key
@ 2004-05-17 16:51 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 6+ messages in thread
From: Karsten Hilbert @ 2004-05-17 16:51 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-05-17 16:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-10 17:03 Unique Constraint with foreign Key mohan@physics.gmu.edu
2004-02-10 20:15 ` Bruno Wolff III <bruno@wolff.to>
2004-02-11 23:33 ` Greg Patnude <gpatnude@hotmail.com>
2004-02-17 14:41 ` Jan Wieck <JanWieck@Yahoo.com>
2004-02-17 17:22 ` Greg Patnude <gpatnude@hotmail.com>
2004-05-17 16:51 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
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