Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up exactly twice in the table.

Sometimes, this constraint is violated, i.e., a pair of friends shows up only once in the table. How do I write a query that will identify all such cases (ideally, using reasonably standard SQL)? In other words, I would like the query to return the list of rows in this table, for which there is no corresponding row with the swapped fields.

An additional question: is there any way to enforce this referential integrity in MySQL?

  • Can anyone explain why both my question and @Stuart Moore answer are downvoted? It's my first post on dba.stackexchange, so I'd like to understand the rules. – max Oct 28 '12 at 11:36
  • 1
    That makes 2 of us. I'm fine with being told I'm wrong, but it would be nice to know what I was wrong about so I can learn from it. – Stuart Moore Oct 28 '12 at 13:18
  • @max: Can you post Friends table definition? "Data errors" is quite vague ; I can think about many types of them (Friend1Id=Friend2Id, any of Friend ids is null, no corresponding row in parent table (say Person/User etc), more than 1 row with the same Friend1Id+Friend2Id, a pair of friends shows only once) ? – a1ex07 Oct 28 '12 at 14:58
  • @a1ex07: thx, didn't realize I was imprecise. I only want to find the cases where "a pair of friends shows only once". Updated the question. – max Oct 28 '12 at 17:42
  • @max: Then I guess Stuart Moore's approach should work for you to identify such cases. To prevent them in the future, I'd create a stored procedure that inserts/deletes 2 records within one transaction, and limit write access to the table , so only this procedure can write into it. – a1ex07 Oct 28 '12 at 18:58

To find the rows, use a left outer join:

    a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID 
    Friends a left join Friends b 
        on (a.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID)
    b.friend1ID IS NULL ;
| improve this answer | |
  • 1
    It wasn't me who downvoted your answer, but I can tell that there is absolutely no point in selecting b.Friend1Id and b.Friend2Id (they are always null). Also, since OP didn't mention that there is a primary key(or unique constraint) on Friends(Friend1Id, Friend2Id), I'd suggest that data errors may include duplicates of (Friend1Id, Friend2Id). Anyway, I think your answer doesn't deserve downvote, so you get upvote from me... – a1ex07 Oct 28 '12 at 14:36
  • 1
    I realize that. But there are many possible 'incorrect' writes ,just ('fred,jason'), or ('fred','fred'), or (null,null)... Also, in your query you have WHERE b.freind1ID is null which is correct, but it makes useless selecting b.Friend1ID, b.Friend2ID -it is the same as NULL,NULL... – a1ex07 Oct 28 '12 at 15:20

The simplest approach is to store each relationship exactly once, and enforce that with a check constraint Friend1

CREATE VIEW AllFriendships
SELECT Friend1, Friend2 FROM Friendships
SELECT Friend1 AS Friend2, Friend2 AS Friend1 FROM Friendships

If, however, you really need the table with both Friend1,Friend2 and Friend2,Friend1, you could create a self-referencing foreign key if MySql's implementation of constraints was more complete:

FOREIGN KEY(Friend1,Friend2) REFERENCES Friendships(Friend2,Friend1)

Once you have created this constraint, you will only be able to insert both rows in one statement. Unfortunately, this is does not work on MySql.

| improve this answer | |
  • Cool, didn't realize this constraint could be so simple. – max Oct 28 '12 at 20:48
  • 1
    @Alex: You know that this FK would not work in MySQL? SQL-Fiddle test – ypercubeᵀᴹ Dec 26 '12 at 22:24
  • MySQL docs, FOREIGN KEY Constraints: "Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. ... InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking." – ypercubeᵀᴹ Dec 26 '12 at 22:38
  • @ypercube thank you for the correction - I just modified the answer accordingly. – A-K Jan 1 '13 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.