pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUNIQUE constraint
8+ messages / 5 participants
[nested] [flat]
* UNIQUE constraint
@ 1999-08-12 05:43 Mihai Barbos <barbos@moon.ro>
1999-08-12 13:23 ` Re: [SQL] UNIQUE constraint Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Mihai Barbos @ 1999-08-12 05:43 UTC (permalink / raw)
To: pgsql-sql@hub.org
Maybe I haven't looked in the roght place but here's my problem:
I've upgraded to 6.5 and I'm trying to use UNIQUE, something like:
create table TEST (
testval something UNIQUE,
.....................,
);
In 6.4.2 it worked OK, but now it seems it has no effect. Is there any
problem with UNIQUE ?
Best regards
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] UNIQUE constraint
1999-08-12 05:43 UNIQUE constraint Mihai Barbos <barbos@moon.ro>
@ 1999-08-12 13:23 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 1999-08-12 13:23 UTC (permalink / raw)
To: Mihai Barbos <barbos@moon.ro>; +Cc: pgsql-sql@hub.org
"Mihai Barbos" <barbos@moon.ro> writes:
> Maybe I haven't looked in the roght place but here's my problem:
> I've upgraded to 6.5 and I'm trying to use UNIQUE, something like:
> create table TEST (
> testval something UNIQUE,
> .....................,
> );
> In 6.4.2 it worked OK, but now it seems it has no effect. Is there any
> problem with UNIQUE ?
I don't see any:
create table TEST (
testval int4 UNIQUE
);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testval_key' for table 'test'
CREATE
insert into test values (1);
INSERT 283276 1
insert into test values (1);
ERROR: Cannot insert a duplicate key into a unique index
You're going to have to be more specific about what problem you
are observing.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] UNIQUE constraint
@ 1999-08-21 15:01 Mihai Barbos <barbos@moon.ro>
1999-08-23 14:01 ` Re: [SQL] UNIQUE constraint Tom Lane <tgl@sss.pgh.pa.us>
1999-09-27 18:55 ` Re: [SQL] UNIQUE constraint Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Mihai Barbos @ 1999-08-21 15:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org
My fault, the correct situation is
create table TEST (
testkey something PRIMARY KEY,
testval something UNIQUE,
.....................
);
PRIMARY KEY works OK, but UNIQUE is ignored.
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mihai Barbos <barbos@moon.ro>
Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
Date: Thursday, August 12, 1999 4:25 PM
Subject: Re: [SQL] UNIQUE constraint
>"Mihai Barbos" <barbos@moon.ro> writes:
>> Maybe I haven't looked in the roght place but here's my problem:
>> I've upgraded to 6.5 and I'm trying to use UNIQUE, something like:
>> create table TEST (
>> testval something UNIQUE,
>> .....................,
>> );
>> In 6.4.2 it worked OK, but now it seems it has no effect. Is there any
>> problem with UNIQUE ?
>
>I don't see any:
>
>create table TEST (
>testval int4 UNIQUE
>);
>NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testval_key'
for table 'test'
>CREATE
>insert into test values (1);
>INSERT 283276 1
>insert into test values (1);
>ERROR: Cannot insert a duplicate key into a unique index
>
>You're going to have to be more specific about what problem you
>are observing.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] UNIQUE constraint
1999-08-21 15:01 Re: [SQL] UNIQUE constraint Mihai Barbos <barbos@moon.ro>
@ 1999-08-23 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 1999-08-23 14:01 UTC (permalink / raw)
To: Mihai Barbos <barbos@moon.ro>; +Cc: pgsql-sql@hub.org
"Mihai Barbos" <barbos@moon.ro> writes:
> My fault, the correct situation is
> create table TEST (
> testkey something PRIMARY KEY,
> testval something UNIQUE,
> .....................
> );
> PRIMARY KEY works OK, but UNIQUE is ignored.
Oh, OK: that bug was noticed and fixed a couple of weeks ago. If you
have *both* a PRIMARY KEY and a UNIQUE on a different field, the UNIQUE
gets dropped --- both 6.5 and 6.5.1 have this mistake. See the patch
Thomas Lockhart posted a week or two back.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] UNIQUE constraint
1999-08-21 15:01 Re: [SQL] UNIQUE constraint Mihai Barbos <barbos@moon.ro>
@ 1999-09-27 18:55 ` Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 1999-09-27 18:55 UTC (permalink / raw)
To: Mihai Barbos <barbos@moon.ro>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@hub.org
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> My fault, the correct situation is
> create table TEST (
> testkey something PRIMARY KEY,
> testval something UNIQUE,
> .....................
> );
>
> PRIMARY KEY works OK, but UNIQUE is ignored.
This is now fixed.
create table TEST (
testkey int PRIMARY KEY,
testval int UNIQUE
);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test_pkey'
for table 'test'
NOTICE: CREATE TABLE/UNIQUE will create implicit index
'test_testval_key' for table 'test'
CREATE
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 8+ messages in thread
* UNIQUE constraint
@ 2004-08-06 18:25 Sascha Ziemann <ziemann@secunet.de>
2004-08-08 23:55 ` Re: UNIQUE constraint Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sascha Ziemann @ 2004-08-06 18:25 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a question about the UNIQUE constraint. The documentation
describes this example:
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
But it is not clean to me. Does the above example mean that the list
of pairs must be unique or is it only a short version for this
constraint:
CREATE TABLE example (
a integer UNIQUE,
b integer,
c integer UNIQUE
);
Does the following table fullfill the UNIQUE clause of the example
from the Postgres documentation?
a b c
-----
1 2 3
1 1 1
cu Sascha
--
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408 Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10 24E4 5004 7C27 6E9A 9698
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: UNIQUE constraint
2004-08-06 18:25 UNIQUE constraint Sascha Ziemann <ziemann@secunet.de>
@ 2004-08-08 23:55 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-09 09:41 ` Re: UNIQUE constraint Sascha Ziemann <ziemann@secunet.de>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Glaesemann @ 2004-08-08 23:55 UTC (permalink / raw)
To: Sascha Ziemann <ziemann@secunet.de>; +Cc: pgsql-sql
On Aug 7, 2004, at 3:25 AM, Sascha Ziemann wrote:
> CREATE TABLE example (
> a integer,
> b integer,
> c integer,
> UNIQUE (a, c)
> );
>
> But it is not clean to me. Does the above example mean that the list
> of pairs must be unique
Yes.
> Does the following table fullfill the UNIQUE clause of the example
> from the Postgres documentation?
>
> a b c
> -----
> 1 2 3
> 1 1 1
Yes.
For example,
test=# create table example (a integer, b integer, c integer, unique
(a,c));
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"example_a_key" for table "example"
CREATE TABLE
test=# insert into example (a,b,c) values (1,2,3);
INSERT 5935749 1
test=# insert into example (a,b,c) values (1,1,1);
INSERT 5935750 1
test=# insert into example (a,b,c) values (1,3,3);
ERROR: duplicate key violates unique constraint "example_a_key"
test=# select a,b,c from example;
a | b | c
---+---+---
1 | 2 | 3
1 | 1 | 1
(2 rows)
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: UNIQUE constraint
2004-08-06 18:25 UNIQUE constraint Sascha Ziemann <ziemann@secunet.de>
2004-08-08 23:55 ` Re: UNIQUE constraint Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-09 09:41 ` Sascha Ziemann <ziemann@secunet.de>
0 siblings, 0 replies; 8+ messages in thread
From: Sascha Ziemann @ 2004-08-09 09:41 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
Michael Glaesemann <grzm@myrealbox.com> writes:
> On Aug 7, 2004, at 3:25 AM, Sascha Ziemann wrote:
> > CREATE TABLE example (
> > a integer,
> > b integer,
> > c integer,
> > UNIQUE (a, c)
> > );
> >
> > But it is not clean to me. Does the above example mean that the list
> > of pairs must be unique
>
> Yes.
Do you know wheather this is part of the SQL standard? I have to
write an application that works on Postgresql and Oracle. Is this the
same on Oracle?
Sorry for asking but right now I have no access to the database to
test it.
cu Sascha
--
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408 Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10 24E4 5004 7C27 6E9A 9698
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-08-09 09:41 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-12 05:43 UNIQUE constraint Mihai Barbos <barbos@moon.ro>
1999-08-12 13:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-21 15:01 Re: [SQL] UNIQUE constraint Mihai Barbos <barbos@moon.ro>
1999-08-23 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-27 18:55 ` Bruce Momjian <maillist@candle.pha.pa.us>
2004-08-06 18:25 UNIQUE constraint Sascha Ziemann <ziemann@secunet.de>
2004-08-08 23:55 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-09 09:41 ` Sascha Ziemann <ziemann@secunet.de>
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