CODEWITHSUNDEEP
postgresqldatabase-design
hgl
hgl

Reputation: 2214

How to design a table where it self-referencs and the order doesn't matter

Say I have a list of users, and I want to record hand shaking between them:

CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    name VARCHAR(70)
);

CREATE TABLE hand_shaking (
    user1 INTEGER NOT NULL REFERENCES user,
    user2 INTEGER NOT NULL REFERENCES user
);

The user table has data like:

1,Sarah
2,John
3,Alex

and the hand_shaking table can have data like:

1,2

which means Sarah and John shook hands.

Now the question is that how should I design the hand_shaking table so that duplicates won't be allowed? For example, if 1,2 is already inserted, 2,1 should fail.

I'm using postgresql, and I tried to declare PRIMARY KEY(user1, user2) on the hand_shaking table, but 2,1 is still allowed after 1,2 has been inserted.

Upvotes: 0

Views: 40

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51529

you can build your check logic using arrays, Eg:

t=# create table so27 (a text,b text, c text);
CREATE TABLE
t=# insert into so27 select 'a','b','c';
INSERT 0 1
t=# insert into so27 select 'a','b','d';
INSERT 0 1
t=# insert into so27 select 'c','a','b';
INSERT 0 1
t=# select *,'{b,c,a}'::text[] @> ('{}'::text[]||a||b||c)::text[] from so27;
 a | b | c | ?column?
---+---+---+----------
 a | b | c | t
 a | b | d | f
 c | a | b | t
(3 rows)

Upvotes: 0

user330315
user330315

Reputation:

Create a unique index that indexes the same values for the two different combinations: 

create unique index only_one_handshake 
    on hand_shaking (least(user1,user2), greatest(user1,user2));

Upvotes: 1

Related Questions