begin Thomas Guettler schrieb:
Create a new unique index for the particular column.

Btw.: Hi Thomas, how are you?


end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Thomas Guettler <guettli.usenet (AT) thomas-guettler (DOT) de> wrote:
I'd recommend that you add a unique constraint rather than an unique index.

Both add an index, but this way there is also a constraint definition.

Syntax:

ALTER TABLE t ADD CONSTRAINT indexname UNIQUE (file);

Yours,
Laurenz Albe

begin Laurenz Albe <invite (AT) spam (DOT) to.invalid> wrote:
Always the same:

test=# create table u (i int, v text);
CREATE TABLE
test=*# alter table u add constraint con_u unique (i);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "con_u" for table "u"
ALTER TABLE
test=*# \d u
Table "public.u"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
v | text |
Indexes:
"con_u" UNIQUE, btree (i)

test=*# rollback;
ROLLBACK
test=# create table u (i int, v text);
CREATE TABLE
test=*# create unique index idx_u on u(i);
CREATE INDEX
test=*# \d u
Table "public.u"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
v | text |
Indexes:
"idx_u" UNIQUE, btree (i)

test=*# rollback;
ROLLBACK
test=# create table u (i int unique, v text);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "u_i_key" for table "u"
CREATE TABLE
test=*# \d u
Table "public.u"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
v | text |
Indexes:
"u_i_key" UNIQUE, btree (i)



I can't see any differences. (except for the index-names)


Okay, another try:

test=# create table u (i int, v text);
CREATE TABLE
test=*# create unique index idx_u on u(i);
CREATE INDEX
test=*# insert into u values (1, 'foo');
INSERT 0 1
test=*# insert into u values (1, 'foo');
ERROR: duplicate key violates unique constraint "idx_u"


We have an unique index and this enforces an unique constraint.



end
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Andreas Kretschmer <akretschmer (AT) spamfence (DOT) net> wrote:
Ok, here's the difference:

test=> CREATE TABLE a(id INTEGER CONSTRAINT id_unique UNIQUE);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "id_unique"
for table "a"
CREATE TABLE

test=> SELECT conname, contype, conkey FROM pg_constraint
test-> WHERE conname='id_unique';

conname | contype | conkey
-----------+---------+--------
id_unique | u | {1}
(1 row)

test=> DROP TABLE a;
DROP TABLE

test=> CREATE TABLE a(id INTEGER);
CREATE TABLE

test=> CREATE UNIQUE INDEX id_unique ON a(id);
CREATE INDEX

test=> SELECT conname, contype, conkey FROM pg_constraint
test-> WHERE conname='id_unique';

conname | contype | conkey
---------+---------+--------
(0 rows)

Yours,
Laurenz Albe

begin Laurenz Albe schrieb:
Oh, thanks for the clarification.



end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Andreas Kretschmer <akretschmer (AT) spamfence (DOT) net> wrote:
Klar, gerne.
Irgendwie komisch, einen Thread mit 3 deutschsprachigen Leuten auf
Englisch zu haben :^)

Laurenz

begin Laurenz Albe schrieb:
Hehe, daß dachte ich auch schon die ganze Zeit...


end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

On 31.05.2007 16:14, Andreas Kretschmer wrote:
Well, it's an English newsgroup - so there might actually be more people
reading this thread than you posters and *they* will likely benefit from
English prose. :-)

Kind regards

robert