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.But if the there is already row with 1,apple,false than it should allow the entry of 2,apple,true.Here 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?