MYSQL--Unique uniqueness Constraint

3. UNIQUE constraint

Constraints uniquely identify each record in a database table.

Create a test table:

CREATE TABLE' test '. ' Info ' (' id ' )INT( One) UNSIGNED not NULLauto_increment, ' Idcard 'VARCHAR( -)UNIQUE  not NULL, ---Add the unique constraint ' mobile ' directly on the fieldVARCHAR( One),  PRIMARY KEY(' id ')) ENGINE=INNODB CHARSET=UTF8 COLLATE=Utf8_estonian_ci;

Add UNIQUE constraint unique:

/*In addition to the above method of adding a unique constraint, if you need to name a unique constraint and define a unique constraint for more than one column, write the following: The Uc_idcard constraint is a unique constraint that is synthesized by the mobile and idcard groups. */CREATE TABLE' test '. ' Info ' (' id ' )INT( One) UNSIGNED not NULLauto_increment, ' Idcard 'VARCHAR( -)UNIQUE, ' mobile 'VARCHAR( One),  PRIMARY KEY(' id '),CONSTRAINTUc_idcardUNIQUE(Mobile,idcard)) ENGINE=INNODB CHARSET=UTF8 COLLATE=Utf8_estonian_ci; If a unique constraint is added in the presence of a table
--no-name constraintALTER TABLE' Info 'ADD UNIQUE(' Idcard ');
--uc_idcard constraints synthetic unique constraints by mobile and Idcard groupsALTER TABLE' Info 'ADD CONSTRAINTUc_idcardUNIQUE(Mobile,idcard);

Revoke a UNIQUE constraint

-- remove a constraint from a specified field ALTER TABLE DROP INDEX  --  Remove the name constraint ALTERTABLEDROPINDEX

To add data to a UNIQUE constraint:

--add a basic dataINSERT  into' Info ' (' Idcard ', ' mobile ')VALUES('99999','1111');--Add a idcard duplicate recordINSERT  into' Info ' (' Idcard ', ' mobile ')VALUES('99999','2222'); Result: Error code:1062DUPLICATE Entry'99999'  for KEY 'Idcard';--add NULL in a unique constraintINSERT  into' Info ' (' Idcard ', ' mobile ')VALUES(NULL,'1111');--add null againINSERT  into' Info ' (' Idcard ', ' mobile ')VALUES(NULL,'2222'); Result: ID Idcard mobile-------------------------     6(NULL)1111         7(NULL)2222
 Reason: Because  null ! = null 

MYSQL--Unique uniqueness Constraint