MySQL add primary key, index

Source: Internet
Author: User
Tags create index

View Index

SHOW INDEX from database table name

For example: SHOW INDEX from Order_info;

Add index

ALTER TABLE Database add index index name (database field name)

Primary key Index

ALTER TABLE ' table_name ' ADD PRIMARY KEY (' column ')

For example: ALTER TABLE order_info ADD PRIMARY KEY (order_id);

Unique (single index)

ALTER TABLE ' table_name ' ADD UNIQUE (' column ')

Index (normal index)
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column ')
Fulltext (full-text index)
ALTER TABLE ' table_name ' ADD fulltext (' column ')
Multi-column Index
ALTER TABLE ' table_name ' ADD index index_name (' Column1 ', ' column2 ', ' Column3 ') 1. Normal index.
This is the most basic index and it has no limitations. It is created in the following ways:
(1) Creating index: Create index IndexName on TableName (tablecolumns (length)); if it is a Char,varchar type, length can be less than the actual length of the field, if it is blob and TEXT Type, the same length must be specified.
(2) Modify table structure: ALTER tableName ADD INDEX [IndexName] On (tablecolumns (length))
(3) directly specify when creating the table: Create TABLE TableName ([...], INDEX [IndexName] (tablecolumns (length));

2. Unique index.
It is similar to the previous "normal index", except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:
(1) Creating an index: Create UNIQUE index indexname on tableName (tablecolumns (length))
(2) Modify table structure: ALTER tableName ADD UNIQUE [IndexName] On (tablecolumns (length))
(3) directly specify when creating the table: Create TABLE TableName ([...], UNIQUE [IndexName] (tablecolumns (length));

3. Primary KEY index
It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is 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 the ALTER command.

MySQL add primary key, index

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.