MySQL WAT

mysql_wat.sql

mysql> create database unique_test;
Query OK, 1 row affected (0.00 sec)


mysql> use unique_test;
Database changed

mysql> create table table_with_unique_index (name TEXT, foo INT, bar INT);
Query OK, 0 rows affected (0.02 sec)

mysql> create unique index name_foo_bar ON table_with_unique_index (name(10), foo, bar);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into table_with_unique_index VALUES("bob", 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into table_with_unique_index VALUES("bob", 1, 1);
ERROR 1062 (23000): Duplicate entry 'bob-1-1' for key 'name_foo_bar'

# That makes sense, let's try it with a NULL value:

mysql> insert into table_with_unique_index VALUES("bob", 1, NULL);
Query OK, 1 row affected (0.01 sec)

# Ok, first record that has these three values.

mysql> insert into table_with_unique_index VALUES("bob", 1, NULL);
Query OK, 1 row affected (0.00 sec)

# W

mysql> insert into table_with_unique_index VALUES("bob", 1, NULL);
Query OK, 1 row affected (0.00 sec)

# T

mysql> insert into table_with_unique_index VALUES("bob", 1, NULL);
Query OK, 1 row affected (0.00 sec)

# F


mysql> select * from table_with_unique_index;
+------+------+------+
| name | foo  | bar  |
+------+------+------+
| bob  |    1 |    1 |
| bob  |    1 | NULL |
| bob  |    1 | NULL |
| bob  |    1 | NULL |
| bob  |    1 | NULL |
+------+------+------+
5 rows in set (0.00 sec)

mysql>