Search code examples
sqlmysqlmysql5

Unique Key in MySql

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.

Solution

  • 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);