In MySQL, is auto_increment confused?

Source: Internet
Author: User
Tags mysql create mysql create table

For an auto_increment?

1) What is its constraint capability? Is it as strong as not null? When a field is auto_inrement and an ID is provided to it, should the database layer not report an error?



The basic syntax of the create table statement in MySQL is:

  1. Create [temporary] Table [if not exists] tbl_name [(create_definition,...)]
  2. [Table_options] [select_statement]

Temporary: This keyword indicates that the table created with MySQL create table is a temporary table, which will automatically disappear after the end of the current session. Temporary tables are mainly used in stored procedures. For MySQL that does not currently support stored procedures, this keyword is generally not used.

If not exists: in fact, the create table operation is performed only when the table does not exist. This option can be used to prevent the table from being created again.

Tbl_name: name of the table you want to create. The table name must comply with the identifier rules. Generally, only letters, numbers, and underscores are used in the table name. For example, titles, our_sales, and my_user1 should be regarded as standardized table names.

Create_definition: This is the key part of the MySQL create table statement. This section defines the attributes of each column in the table.

The basic statements for create_definition are:

  1. Col_name type [not null | null] [Default default_value] [auto_increment]
  2. [Primary key] [reference_definition]
  3. Or primary key (index_col_name ,...)
  4. Or key [index_name] (index_col_name ,...)
  5. Or index [index_name] (index_col_name ,...)
  6. Or unique [Index] [index_name] (index_col_name ,...)
  7. Or [constraint symbol] foreign key index_name (index_col_name ,...)
  8. [Reference_definition]
  9. Or check (expr)

Col_name: name of the column in the table. It must comply with the identifier rules and be unique in the table.

Type: the data type of the column. Some data types need to specify the length N and enclose them in parentheses. For details about the data types currently provided by MySQL, see MySQL advanced _ column type.

Not null | NULL: Specifies whether the column can be null. If neither null nor not null is specified, the column is considered null.

Default default_value: Specify the default value for the column. If no default value is specified for the column, MySQL automatically assigns one. If the column can take null as the value, the default value is null. If the column is declared as not null, the default value depends on the column type:

1. For numeric types that do not declare the auto_increment attribute, the default value is 0. For an auto_increment column, the default value is the next value in the sequence.

2. For date and time types except timestamp, the default value is the appropriate "zero" value of this type. For the first timestamp column in the table, the default value is the current date and time.

3. For string types except Enum, the default value is null. For Enum, the default value is the first enumerated value.

Auto_increment: set this column to have the auto-increment attribute. This attribute can be set only for integer columns. When you insert a null value or 0 to an auto_increment column, the column is set to value + 1, where value is the maximum value of this column in the previous table. The auto_increment sequence starts from 1. Each table can have only one auto_increment column, and it must be indexed.

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: 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.