Difference in use Index Unique and Unique Constraint in MySQL?

19

I would like to know what behavior / differences between a Unique Index and Unique Constraints for MySQL ?

Many should think that this does not make a difference in the database, but it does! And not only for the DBA / Administrator, but for developers too as it can influence how a code is written.

As a reference of what may be so different and what I'm interested in knowing, here's a link to what it's like in a Informix database, but not MySQL how does it work?

Link, similar situation in another engine: Difference in use Unique and Unique Constraint index in Informix?

    
asked by ceinmart 21.12.2013 в 12:48
source

3 answers

8

In MySQL it's the same .

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

According to documentation :

KEY and INDEX are synonyms.

  

KEY is a synonym for INDEX. The key attribute PRIMARY KEY can   KEY POINTS are given in a column definition. This   was implemented for compatibility with other database systems.

The nomenclature for index 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 check for yourself, run the dump of the table structure, and you will see that for either index or constraint , it is the same thing.

But if you still want to take the final test, compare the bank's schema , and you will see that there is no difference.

    
answered by 12.01.2014 в 05:11
5

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

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

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 such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This restriction does not apply to NULL values, except for the BDB store engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL .

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

    
answered by 31.03.2014 в 20:38
0

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

A primary key: Can be used for foreign key relationships with other tables. (Can not contain null records but autoincrement can be used.)

    
answered by 13.11.2017 в 21:06