1

Is it possible to create a MySQL constraint to force a unique value between two columns in the same row?

For example:

CREATE TABLE Buddies (
  User1 int,
  User2 int,
) Type = InnoDB;

How would I disallow...

INSERT INTO MyTable SET User1 = '5', User2 = '5' ?

That user would be very lonely otherwise :)

EDIT: the point here is that I would like to do this purely in MySQL and not in the application layer, if possible.

Recovering Nerdaholic
  • 687
  • 1
  • 7
  • 23

1 Answers1

0

Hi this code makes two column on a row to be unique:

ALTER TABLE 'tableName' ADD UNIQUE( 'column1', 'column2');

With the code above, if you insert value to column1 and column2 identically, it will not be inserted. For example:

$value1 = 1;
$value2 = 2;
$value3 = 3;
mysql_query("INSERT INTO tableName (column1, column2) VALUES ('$value1', '$value2')"); //This will work.
mysql_query("INSERT INTO tableName (column1, column2) VALUES ('$value1', '$value3')"); //This will work.
mysql_query("INSERT INTO tableName (column1, column2) VALUES ('$value1', '$value2')"); //This WILL NOT work THE SECOND time around because of the unique constraint.

Or refer to this code:

$value1 = 1;
$value2 = 2;

$resultSet = mysql_query("SELECT column1 FROM tableName WHERE column1 = '$value1'");
$rows = mysql_num_rows($resultSet);

if($rows >= 1){
    echo $value1 . " already exist.";
}
else{
    $query = mysql_query("INSERT INTO tableName (column1, column2) VALUES('$value1','$value2')");

    if(){
        echo "Value inserted.";
    }else echo mysql_error();
}

The code above checks the value of $value1 before inserting the two values to determined if $value1 already exist.

Hope this help you. :-)

Kiel Labuca
  • 1,223
  • 9
  • 13
  • I realize I could do this in the application layer, but I was hoping that it could be done purely in MySQL. – Recovering Nerdaholic Jan 27 '14 at 03:29
  • @KielLabuca . . The code you mean creates a unique index with two fields. It allows (5, 5) to be inserted once into the table, but not twice. – Gordon Linoff Jan 27 '14 at 03:50
  • @GordonLinoff -- Exactly Mr. Linoff, that's what I meant about the first bunch of code above. – Kiel Labuca Jan 27 '14 at 05:04
  • In his question, he mentioned two columns in the same table so that is the code I provide, however if he wants only one column then he could still use the same code just with one parameter, like this: `ALTER TABLE 'tableName' ADD UNIQUE( 'column1' );` – Kiel Labuca Jan 27 '14 at 05:09
  • @KielLabuca . . . You are missing the point. The OP is looking for a check constraint on values in the same row, not across all rows. MySQL does not execute check constraints. – Gordon Linoff Jan 27 '14 at 12:22
  • @Gordon Linoff: yes, this is correct. I know I can do constraints on multiple columns across multiple rows. I want a constraint *between* multiple columns in the *same* row. – Recovering Nerdaholic Jan 28 '14 at 02:30