MySQL unique index unique use method detailed

The purpose of creating a unique index is not to improve access speed, but to avoid duplication of data. A unique index can have more than one but the value of the indexed column must be unique, and the value of the indexed column allows null values. If you can determine that a data column will contain only values that are different from each other, you should use the keyword unique when you create an index for that data column

Define it as a unique index.

How to create a unique cable

Action Sheet

code is as follows	copy code
CREATE TABLE ' Wb_blog ' (           ' id ' smallint (8) unsigned not null,          ' catid ' smallint (5) unsigned not NULL DEFAULT ' 0 ',            ' title ' varchar not NULL DEFAULT ',            ' content ' text not null,           PRIMARY KEY (' id '),          )

1, create a unique cable can be created with the keyword unique with the table

code is as follows	copy code
mysql> CREATE TABLE ' Wb_blog ' (       ->   ' id ' smallint (8) unsigned not null,      ->   ' CatID ' smallint (5) unsigned not NULL DEFAULT ' 0 ',       ->   ' title ' varchar not NULL DEFAULT ',       ->   ' content ' text not Null,&nbs p;    ->   PRIMARY KEY (' id '),       -> & nbsp UNIQUE KEY ' catename ' (' catid ')       ->);    9 Query OK, 0 rows Affe CTED (0.24 sec)

The above code creates a unique index named Catename for the ' CATID ' field of the Wb_blog table

2. Use the Create command after creating the table

The code is as follows	Copy Code
mysql> CREATE UNIQUE INDEX catename on Wb_blog (CATID); Query OK, 0 rows affected (0.47 sec)

If you do not need a unique index, you can delete the

The code is as follows	Copy Code
mysql> ALTER TABLE wb_blog DROP INDEX catename; Query OK, 0 rows affected (0.85 sec)

If you want to add an index

The code is as follows	Copy Code
ALTER TABLE user add unique index (USER_ID,USER_NAME);

Attention

Unique index.

It is similar to the previous "normal index", except that the value of an indexed column must be unique, but a null value is allowed. If it is a combined index, the combination of the column values must be unique. It has the following ways to create:
(1) CREATE INDEX: Create UNIQUE index indexname on tablename (tablecolumns (length))
(2) Modify table structure: ALTER tablename ADD UNIQUE [IndexName] On (tablecolumns (length))
(3) Specify directly when creating a table: Create TABLE TableName ([...], UNIQUE [IndexName] (tablecolumns (length));

3. Primary KEY index

It is a special unique index and does not allow null values. Typically, you create a primary key index at the same time as the table is being built: CREATE TABLE Testindex (I_testid INT not NULL auto_increment,vc_name VARCHAR () not null,primary key (i _testid)); Of course, you can also use alter order.