Creating more than 1 UK on a column?

[becky]

Vertica won't let me create more than one PK constraint on a column:

Code: Select all

dbadmin=> create table s (s1 int not null);
CREATE TABLE
dbadmin=> alter table s add constraint pk1 primary key (s1);
ALTER TABLE
dbadmin=> alter table s add constraint pk2 primary key (s1);
ROLLBACK 4413:  Primary constraint for relation "s" already exists

But I can create more than one UK constraint on a column:

Code: Select all

dbadmin=> alter table s add constraint uk1 unique (s1);
ALTER TABLE
dbadmin=> alter table s add constraint uk2 unique (s1);
ALTER TABLE
dbadmin=> alter table s add constraint uk3 unique (s1);
ALTER TABLE

Is there a reason why I would want multiple UK constraints on the same column???

[JimKnicely]

Hmm. Becky, obviously shows that UK constraints aren't enforced...

[JimKnicely]

Looks like we can violate all three unique constraints all at the same time

Code: Select all

dbadmin=> create table t (c int);
CREATE TABLE
dbadmin=> alter table t add constraint uk1 unique (c);
ALTER TABLE
dbadmin=> alter table t add constraint uk2 unique (c);
ALTER TABLE
dbadmin=> alter table t add constraint uk3 unique (c);
ALTER TABLE
dbadmin=> insert into t values (1);
 OUTPUT
--------
      1
(1 row)

Code: Select all

dbadmin=> insert into t values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select analyze_constraints('t');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
 testvert    | t          | c            | uk2             | UNIQUE          | ('1')
 testvert    | t          | c            | uk3             | UNIQUE          | ('1')
 testvert    | t          | c            | uk1             | UNIQUE          | ('1')
(3 rows)