ADD CONSTRAINT behaviour question
When adding unique keys:
* If you do this, you get two unique keys (7.0.3):
create table test (int4 a, int4 b);
create unique index indx1 on test(a, b);
create unique index indx2 on test(a, b);
Then you get this:
Table "test"
Attribute | Type | Modifier
-----------+---------+----------
a | integer |
b | integer |
Indices: asdf,
asdf2
* If you do this, you only get two unique keys (7.0.3):
create table test (a int4, b int4, unique(a, b), unique(a, b));
Then you get this:
Table "test"
Attribute | Type | Modifier
-----------+---------+----------
a | integer |
b | integer |
Indices: test_a_key,
test_a_key1
* So, does this mean that my ALTER TABLE/ADD CONSTRAINT code should happily
let people define multiple unique indices over the same columns?
* As a corollary, should it prevent people from adding more than one primary
key constraint?
Chris
ps. I know I only tested these on 7.0.3 - but I assume HEAD has similar
behaviour?
You assume wrong.
It's a bad idea to try to develop backend code against back releases.
My bad. I'm at work at the moment and I tried it out here to rejig my
memory before posting. I do remember testing it on HEAD at home and the
create table (.., unique, unique) doesn't duplicate.
Don't worry - it's being developed on HEAD - I was just trying to get out of
figuring out how to detect an already existing index across specified
columns. It's complicated.
Chris
Import Notes
Reply to msg id not found: 7834.994643402@sss.pgh.pa.us | Resolved by subject fallback
