You may want to try creating a trigger that will check for the presence of (col1,col2) existing as (col2,col1)
Here is an example:
use test
drop table if exists ali;
create table ali
(
col1 int not null,
col2 int not null,
primary key (col1,col2)
);
DELIMITER $$
CREATE TRIGGER ali_bi BEFORE INSERT ON ali FOR EACH ROW
BEGIN
DECLARE found_count,newcol1,newcol2,dummy INT;
SET newcol1 = NEW.col1;
SET newcol2 = NEW.col2;
SELECT COUNT(1) INTO found_count FROM ali
WHERE col1 = newcol2 AND col2 = newcol1;
IF found_count = 1 THEN
SELECT 1 INTO dummy FROM information_schema.tables;
END IF;
END; $$
DELIMITER ;
INSERT INTO ali VALUES (1,2);
INSERT INTO ali VALUES (3,4);
INSERT INTO ali VALUES (2,1);
INSERT INTO ali VALUES (4,3);
SELECT * FROM ali;
The trigger is designed to break on purpose when FOUND_COUNT is 1.
Here is the sample executed:
mysql> use test
Database changed
mysql> drop table if exists ali;
Query OK, 0 rows affected (0.03 sec)
mysql> create table ali
-> (
-> col1 int not null,
-> col2 int not null,
-> primary key (col1,col2)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER $$
mysql> CREATE TRIGGER ali_bi BEFORE INSERT ON ali FOR EACH ROW
-> BEGIN
-> DECLARE found_count,newcol1,newcol2,dummy INT;
-> SET newcol1 = NEW.col1;
-> SET newcol2 = NEW.col2;
-> SELECT COUNT(1) INTO found_count FROM ali
-> WHERE col1 = newcol2 AND col2 = newcol1;
-> IF found_count = 1 THEN
-> SELECT 1 INTO dummy FROM information_schema.tables;
-> END IF;
-> END; $$
Query OK, 0 rows affected (0.07 sec)
mysql> DELIMITER ;
mysql> INSERT INTO ali VALUES (1,2);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO ali VALUES (3,4);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO ali VALUES (2,1);
ERROR 1172 (42000): Result consisted of more than one row
mysql> INSERT INTO ali VALUES (4,3);
ERROR 1172 (42000): Result consisted of more than one row
mysql> SELECT * FROM ali;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.00 sec)
mysql>
Give it a Try !!!
CAVEAT This does not work on bulk INSERTs. Only when inserting one row at a time.
I have used this technique and suggested it in other DBA StackExchange Questions
UPDATE 2012-02-29 11:46 EDT
I tried to INSERT the same four rows again.
INSERT INTO ali VALUES (1,2);
INSERT INTO ali VALUES (3,4);
INSERT INTO ali VALUES (2,1);
INSERT INTO ali VALUES (4,3);
SELECT * FROM ali;
Here is what I get
mysql> INSERT INTO ali VALUES (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO ali VALUES (3,4);
ERROR 1062 (23000): Duplicate entry '3-4' for key 'PRIMARY'
mysql> INSERT INTO ali VALUES (2,1);
ERROR 1172 (42000): Result consisted of more than one row
mysql> INSERT INTO ali VALUES (4,3);
ERROR 1172 (42000): Result consisted of more than one row
mysql> SELECT * FROM ali;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.00 sec)
mysql>
This trigger approach work well despite the quirky message.
Best Answer
That is part of one of computing's big philosophical debates. Sometimes it gets as heated amongst information theorists as the vi/emacs thing does amongst Linux developers...
I always avoid using a value that might change as a key or part of a key, in this case player name or server name unless your system has an explicit rule that those properties can not change and must be unique. While you can in some databases use ON UPDATE CASCADE to remove problems due to the value of a PK needing to be updated, this feels unclean to me. If the player name must be unique then you do not really need a separate player ID as playername+servername is a candidate key in the player table too.
BTW: Your player table feels incorrect to me.I would keep servers as a separate entity so the same player record can be linked to multiple servers, so one player who plays in many places does not have to have different records for each server. Something like:
Player Ranking Server -------------- ----------------- -------------- PlayerID (PK) <-- PlayerID (FK, PK) ServerName PlayerName ServerID (FK, PK) --> ServerID (PK) {more columns} PlayerRank {more columns}or
Player Ranking Server -------------- ------------- -------------- PlayerID (PK) <-- PlayerID (FK) ServerName PlayerName ServerID (FK) --> ServerID (PK) {more columns} PlayerRank {more columns} RankID (PK)(probably the former as PlayerID+ServerID is a candidate key and not having the extra ID saves space in both not having the extra data and needing an extra index)
One further point: PlayerRank can not be a unique index on its own as you will have a "rank 1" player on each server that has at least one player, so the unique index will need to be over ServerID+PlayerRank.