How to implement unique constraint on 2 columns

[raghavv]

Hi,
How do you implement unique constraint on 2 columns.i.e consider i have 3 column id,name and active.If the there is a row in a table with 1,apple,true than it should not allow the entry of a row with values 2,apple,true.Bu t if the there is already row with 1,apple,false than it should allow the entry of 2,apple,true.He re the constraint must be applied on name and active column.

Thank you.

[Atli]

Hi.

So basically, there should never be two rows with identical values for both the name and active columns?

If so, consider this:

mysql> CREATE TABLE test (
    ->   `id` Serial Primary Key,
    ->   `name` VarChar(255) Not Null,
    ->   `active` Bool Not Null,
    ->   Unique Key `NameActive` (`name`, `active`)
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO test(name, active) VALUES ('apple', true);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test(name, active) VALUES ('apple', true);
ERROR 1062 (23000): Duplicate entry 'apple-1' for key 3

mysql> INSERT INTO test(name, active) VALUES ('apple', false);
Query OK, 1 row affected (0.00 sec)

Is that what you are after?