mysql many to many relationship

Question

Been reading the tutorial How to handle a Many-to-Many relationship with PHP and MySQL .

In this question I refer to the "Database schema" section which states the following rules:

This new table must be constructed to allow the following:

* It must have a column which links back to table 'A'.
* It must have a column which links back to table 'B'.
* It must allow no more than one row to exist for any combination of rows from table 'A' and table 'B'.
* It must have a primary key.

Now it's crystal clear so far.

The only problem I'm having is with the 3rd rule ("It must allow no more than one row to exist for any combination").
I want this to be applied as well, but it doesn't seem to work this way.

On my test instance of mysql (5.XX) I'm able to add two rows which reflect the same relationship!

For example, if I make this relation (by adding a row):
A to B
It also allows me to make this relation as well:
B to A

So the question is two questions actually:

1) How do I enfore the 3rd rule which will not allow to do the above? Have only one unique relation regardless of the combination.

2) When I'll want to search for all the relations of 'A', how would the SQL query look like?

Note #1: Basically my final goal is to create a "friendship" system, and as far as I understand the solution is a many-to-many table. Suggest otherwise if possible.

Note #2: The users table is on a different database from the relations (call it friendships) table. Therefore I cannot use foreign keys.

Answer 1

For the first question:

1. Create a unique constraint on both columns
2. Make sure you always sort the columns. So if your table has the colummns a and b than make sure that a is less than or equal to b

For the second question:

SELECT
  *
FROM
  many_to_many_table
WHERE
  a = A or b = A

Answer 2

Ok WoLpH was faster, I basically agree (note that you have to create a single constraint on both columns at the same time!). And just to explain why you collide with the rules you mentioned: Typically, A and B are different tables. So the typical example for n:m relations would allow entries (1,0) and (0,1) because they'd be refering to different pairs. Having table A=table B is a different situation (you use A and B as users, but in the example they're tables).

Answer 3

It sounds like you want a composite primary key.

CREATE TABLE relationship (
     A_id INTEGER UNSIGNED NOT NULL,
     B_id INTEGER UNSIGNED NOT NULL,
     PRIMARY KEY (A_id, B_id)
);

This is how you setup a table so that there can only ever be one row that defines tables A and B as related. It works because a primary key has to be unique in a table so therefore the database will allow only one row with any specific pair of values. You can create composite keys that aren't a primary key and they don't have to be unique (but you can create a unique non-primary key, composite or not), but your specification requested a primary key, so that's what I suggested.

You can, of course, add other columns to store information about this specific relationship.