MySql, as many other RDBMS’s, provides a lot of interesting functionalities.
The following example will show to you how to create a unique index from two columns in a table. This way, when you’ll insert new records into the table MySql will check for you if the record inserted is already into the table, and in this case MySql will not insert the record.
Let’s start creating a new table with a primary key an two integer fields:
CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`val1` int(10) unsigned NOT NULL,
`val2` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
);
Now insert two records with the same values for val1 and val2 fields.
INSERT INTO `mytable` (`id`, `val1`, `val2`) VALUES (NULL, '1', '1'), (NULL, '1', '1');
and the content of the table will be the following:
id val1 val2
1 1 1 2 1 1
Now remove the second row, and alter the table as shown in the following line:
ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`val1`, `val2`);
Now try to insert again two records with the same values for val1 and val2 fields:
INSERT INTO `mytable` (`id`, `val1`, `val2`) VALUES (NULL, '2', '2'), (NULL, '2', '2');
the query is refused by MySql with the following error:
#1062 - Duplicate entry '2-2' for key 'unique_index'
Naturally, different records will be normally inserted into the DB.
Gg1