Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/#!/StackDBAs/status/262660503229317120
edited tags
Link
A-K
A-K
  • 7.4k
  • 3
  • 34
  • 51
added 180 characters in body
Source Link
max
max
  • 141
  • 4

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.

Due to data errorsSometimes, this constraint may sometimes beis violated, i.e., a pair of friends shows up only once in the table. How do I write a query that will identify anyall 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?

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.

Due to data errors, this constraint may sometimes be violated. How do I write a query that will identify any such cases (ideally, using reasonably standard SQL)?

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

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?

Source Link
max
max
  • 141
  • 4

Finding violations of the symmetry constraint

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.

Due to data errors, this constraint may sometimes be violated. How do I write a query that will identify any such cases (ideally, using reasonably standard SQL)?

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