MySQL unique index unique use method detailed

Source: Internet
Author: User
Tags create index

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.