Difference in Index Unique and Unique Constraint usage in Mysql?

Question

I would like to know the behavior/differences between a Indice Unique and Unique Constraints to the Mysql ?

Many may think that it makes no difference in the database, but it does! And not only for the DBA/Administrator, but also for the developers because it can influence how a code is written.

---

As a reference of what may be so different and what I’m interested to know, below is a link of how it is in a bank Informix , but in the Mysql how it works?

Link, similar situation on other engine: Difference in Use Index Unique and Unique Constraint in Informix?

Answer 1

In Mysql is the same thing.

ALTER TABLE tabela ADD CONSTRAINT UNIQUE KEY is the same thing as CREATE UNIQUE INDEX

According to the documentation:

KEY and INDEX are synonymous.

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when Given in a column Definition. This was implemented for Compatibility with other database systems.

The nomenclature for indexes is to use the same name as the first field, and if it already exists, add _2, _3 and so on.

In Mysql, the name of a PRIMARY KEY is PRIMARY. For other Indexes, if you do not assign a name, the index is Assigned the same name as the first Indexed column, with an optional suffix (_2, _3, ...) to make it Unique. You can see index Names for a table using SHOW INDEX FROM tbl_name.

To prove yourself, run the table structure dump, and you will see that both to index or Constraint, is the same thing.

But if you still want to get the final proof, compare the schema from the bank, and you’ll see there’s no difference.

Answer 2

KEY and INDEX are synonyms in Mysql.
They mean the same thing. In the database you would use indexes to improve data recovery speed.
An index is typically created in columns used in clauses JOIN, WHERE e ORDER BY.
Important : You may only have one primary key per table, but several unique restrictions.

There is a very important difference between a Unique Index (Mysql responding to a "uniqueness constraint") and a primary key in Mysql.
Take a look at this:
Create a table t with a indice unique in the columnas a,b (The combination of columns a,b should uniquely identify any tuple in the table, certain?)

CREATE TABLE t (
  a int,
  b int,
  c int,
  UNIQUE KEY a (a,b)
);

Now let’s enter data:

mysql> insert into t (a,b,c)values(1,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t (c)values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t (a,c)values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t (b,c)values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t (b,c)values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t (a,b,c)values(1,2,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'a'
mysql> select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
| NULL | NULL |    1 |
|    1 | NULL |    1 |
| NULL |    1 |    1 |
| NULL |    1 |    1 |
+------+------+------+
5 rows in set (0.00 sec)

mysql>

A unique index creates a constraint in such a way that all values in the index must be distinct. An error occurs if you try to add a new line with a key value that matches an existing line. This restriction does not apply to NULL values, except for the storage engine BDB. For other engines, an index UNIQUE allows multiple NULL values for columns that can contain NULL.

Performance Indice Unique vs Unique Constraints: With MyISAM as the engine, there should not be a performance difference between the Indice Unique vs Unique Constraints.
The MyISAM does not treat them differently.
If you were using the engine InnoDB, however, there would be a difference, InnoDB stores data in primary key order.

Answer 3

The key: ensures the uniqueness of information in your table. (Generally unique keys may also have null records, so they may not be primary key.)


A Primary key: Can be used for Foreign key relationships with other tables. (Cannot contain null records but can be used autoincrement).