Unique Key in MySql

Question

I have a table with four Columns: Col1, Col2, Col3, and Col4.
Col1, Col2, Col3 are strings, and Col4 is a integer primary key with Auto Increment. Now my requirement is to have unique combination of Col2 and Col3.

I mean to say like.

Insert into table(Col1, Col2, Col3) Values ('val1', 'val2', 'val3');
Insert into table(Col1, Col2, Col3) Values ('val4', 'val2', 'val3');

the second statement has to throw error as the same combination of 'val2','val3' is present in the table. But i cant make it as a primary key as i need a auto increment column and for that matter the col4 has to be primary. Please let me know a approach by which i can have both in my table.

Answer 1

You can do this by creating a unique index on those fields.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

From that doc:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Answer 2

You can set in the database schema a requirement that a combination of two or more keys be unique. This can be found here:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

This could be done with a command such as

ALTER TABLE YourTable ADD UNIQUE (Col2,Col3);

Answer 3

I would check for these values as part of your insert logic. Before you insert, write something like...

if exists
    select 1 from table
    where col2 = inputCol2 and col3 = inputCol3
then -- combo already exists
    do nothing / set error
else -- combo doesnt exist yet
    insert into table