Thread: two-column primary key (not the typical question)
I need a suggestion. I need a two-column primary key that does not depend on the order of the entries. That is, for the purposes of the key:
PKColA PKColB
foo bar
bar foo
is not valid.
Any help would be appreciated.
On Fri, Jul 6, 2012 at 7:00 PM, Chip Nowacek <chip@twostewards.com> wrote:
I need a suggestion. I need a two-column primary key that does not depend on the order of the entries. That is, for the purposes of the key:PKColA PKColBfoo barbar foo
What about a unique functional index which would return some sort of ordered representation of this along with NOT NULL constraints?
I don't know if you can do it as a simple primary key, but you can probably get unique functional indexes to do the main work for you.
Best Wishes,
Chris Travers
On Fri, Jul 6, 2012 at 10:00 PM, Chip Nowacek <chip@twostewards.com> wrote:
I don't think it's possible using PKeys. It can be done with unique expression index combined with NOT NULL constraints.
Here's a working example:
postgres=# create table test3( a varchar, b varchar);
CREATE TABLE
postgres=# create unique index on test3 ((case when a < b then a || b else b || a end));
CREATE INDEX
postgres=# alter table test3 alter a set not null, alter b set not null;
ALTER TABLE
postgres=# insert into test3 values('foo', 'bar');
INSERT 0 1
postgres=# insert into test3 values('foo', 'bar');
ERROR: duplicate key value violates unique constraint "test3_case_idx"
DETAIL: Key ((
CASE
WHEN a::text < b::text THEN a::text || b::text
ELSE b::text || a::text
END))=(barfoo) already exists.
postgres=# insert into test3 values('bar', 'foo');
ERROR: duplicate key value violates unique constraint "test3_case_idx"
DETAIL: Key ((
CASE
WHEN a::text < b::text THEN a::text || b::text
ELSE b::text || a::text
END))=(barfoo) already exists.
postgres=#
Best regards,I need a suggestion. I need a two-column primary key that does not depend on the order of the entries. That is, for the purposes of the key:PKColA PKColBfoo barbar foois not valid.
I don't think it's possible using PKeys. It can be done with unique expression index combined with NOT NULL constraints.
Here's a working example:
postgres=# create table test3( a varchar, b varchar);
CREATE TABLE
postgres=# create unique index on test3 ((case when a < b then a || b else b || a end));
CREATE INDEX
postgres=# alter table test3 alter a set not null, alter b set not null;
ALTER TABLE
postgres=# insert into test3 values('foo', 'bar');
INSERT 0 1
postgres=# insert into test3 values('foo', 'bar');
ERROR: duplicate key value violates unique constraint "test3_case_idx"
DETAIL: Key ((
CASE
WHEN a::text < b::text THEN a::text || b::text
ELSE b::text || a::text
END))=(barfoo) already exists.
postgres=# insert into test3 values('bar', 'foo');
ERROR: duplicate key value violates unique constraint "test3_case_idx"
DETAIL: Key ((
CASE
WHEN a::text < b::text THEN a::text || b::text
ELSE b::text || a::text
END))=(barfoo) already exists.
postgres=#
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
EnterpriseDB Corporation
The Enterprise PostgreSQL Company