Re: Links between rows in a table

>> It would probably be better to always have either both or neither of
>> the symmetric relationships in the table. You could make a set of  
>> triggers
>> to enforce this.
Because your relation is symmetric, you should not name them "user" and  
"friend".The duplication is useless if you add a constraint : see this

create table friendship (user_id_1 integer    references ... on delete cascade,user_id_2 integer references ... on
deletecascade,
 
CHECK( user_id_1 < user_id_2 )
);
user_id_1 < user_id_2 means :- a user can't be his own friend- only one row per friend- when you want to know if A is
friendof B, no need to make two selects,  
 
just select where user_id_1 = min(user_id_A, user_id_B) AND user_id_2 =  
max(user_id_A, user_id_B)
To get the list of friends for a user, you still need the union, but that  
is no real problem. Making two queries will be marginally slower than one  
query on a bigger table, but youu save precious cache space, so in the end  
it could be faster.