How to add or delete a primary key in mysql

Source: Internet
Author: User
Tags mysql commands

It is very basic to add or delete a primary key in mysql. The following describes how to add or delete a primary key in mysql. If you are a new user of mysql database, take a look.

Modify the character set of mysql:
When installing mysql5.0, you can set the mysql character set. Generally, the utf8 character set is used.

1. view the SQL statement used to create a table "tablename"

Show create table tablename; // view the SQL statement for creating a table "tablename"
| Maos_mail_batch | create table 'maos _ mail_batch '(
'Id' varchar (32) character set latin1 not null,
'Batch _ time' varchar (30) character set latin1 not null,
'Batch _ INTRODUCE 'varchar (2000) character set latin1 default NULL,
'Batch _ Sum' int (11) default NULL,
'Batch _ state' varchar (1) character set latin1 default NULL,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8 |

2. Modify the character set of databases and tables
Alter database maildb default character set utf8; // modify the character set of the database
Alter table mailtable default character set utf8; // modify the character set of the table
If you want to change the default Character Set of the table and all character columns CHAR, VARCHAR, TEXT) to the new character set, use the following statement:
Alter table tbl_name convert to character set charset_name;
Warning the previous operation converts the column type between character sets. If one column uses a character set such as latin1), but the stored value actually uses another character set such as utf8, this is not what you want. In this case, you must perform the following operations on such columns.
Alter table t1 CHANGE c1 c1 BLOB;
Alter table t1 CHANGE c1 c1 text character set utf8;
This method can achieve this function because the conversion does not occur when you convert to the BLOB column or from the BLOB column.

3. Primary Key Issues in mysql:

Two primary keys of Mysql. Primary key and not null auto_incriment when creating a mysql table, a primary key is added to a field. The Primary key does not need to be inserted during data insert. mysql automatically adds 0, however, if the value is not set during the second insert operation, mysql database still defaults to 0, which leads to duplicate primary keys. This is not acceptable. When the primary key is defined, the primary key value must be filled in when the data is inserted.

When creating a mysql table, a primary key not null auto_increment = 1 is added to a field;
This is also a primary key. The growth starts with 1. This field does not need to be filled in. mysql database automatically fills in the value without the primary key.
Alter table tb add primary key (id );
Alter table tb change id int (10) not null auto_increment = 1;

4. Delete the auto-increment primary key id

Delete auto-incrementing primary keys first
Alter table tb change id int (10); // Delete auto-Increment
Alter table tb drop primary key; // Delete the primary key

The above describes how to add and delete a primary key in mysql.

How to Set MySql access restrictions

Six Methods for changing the password of MySQL

Common mysql commands in linux

Nine common mysql commands

Common MySQL auto-increment Fields

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.