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

11788381-php-mysql-development

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