PHP Archive

Using the UNIQUE Modifier

Using MySQL's built-in validation mechanisms has an important advantage: it makes it easy to perform certain types of validation that would be lengthy and time-consuming to write code for. Consider, for example, the situation of ensuring that a particular field contains only unique values. MySQL makes it possible to do this, simply by attaching a UNIQUE modifier to the field, as in the following example:

mysql> CREATE TABLE users (

-> username VARCHAR(50) NOT NULL UNIQUE

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO users (username) VALUES ('tim');

Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO users (username) VALUES ('jon');

Query OK, 1 row affected (0.00 sec)

Now, if you attempt to enter another record with the value tim in the username field, MySQL will reject your entry with an error:

mysql> INSERT INTO users (username) VALUES ('tim');

ERROR 1062: Duplicate entry 'tim' for key 1

If you had to perform this type of validation at the application layer, the only way to do it would be to select all the records in the table, scan the username field to obtain a list of all values present in it, and check the user's input against each to eliminate duplication. Needless to say, this is expensive, both in terms of CPU cycles and time. Fortunately, the UNIQUE modifier renders it unnecessary.