Access build table SQL statement CREATE TABLE set auto-Grow column keyword AutoIncrement use method

Source: Internet
Author: User

Access build table SQL statement CREATE TABLE set auto-Grow column keyword AutoIncrement use method

SQL AUTO INCREMENT Field
uto-increment will generate a unique number when the new record is inserted into the table.


AUTO INCREMENT Field

We typically want to automatically create a value for the primary key field each time a new record is inserted.

We can create a auto-increment field in the table.


Syntax for MySQL

The following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons (p_id int not NULL auto_increment,lastname varchar (255) not null,firstname varchar (255), Address Varch AR (255), City varchar (255), PRIMARY KEY (p_id))
Syntax for SQL Server

The following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons (p_id int PRIMARY KEY identity,lastname varchar (255) not null,firstname varchar (255), Address varchar ( 255), City varchar (255))

MS SQL uses the IDENTITY keyword to perform auto-increment tasks.

By default, the start value of the IDENTITY is 1, and each new record is incremented by 1.

To specify that the "p_id" column starts at 20 and increments by 10, change the identity to identity (20,10)


Syntax for Access

The following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons (p_id autoincrement PRIMARY key,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

MS Access uses the AutoIncrement keyword to perform auto-increment tasks.

By default, the start value of AutoIncrement is 1, and each new record is incremented by 1.

To specify the "p_id" column to start at 20 and increment by 10, change the AutoIncrement to AutoIncrement (20,10)


Attention:

1. AutoIncrement itself is an integral type, so do not set int.
2. Auto increment, of course, is to help you automatically enter the contents of that field, so do not set it not NULL.
3. Auto increment, there is certainly no repetition, so you should set it before setting the primary key.

The following statement fails to execute:

CREATE TABLE Persons1 (p_id int not null PRIMARY KEY  autoincrement,lastname varchar (255) Not null,firstname varchar (25 5), Address varchar (255), City varchar (255))

Return error message: syntax error in the CREATE TABLE statement.
Will not tell you exactly where there is a mistake, this is more than the pit father.


Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Access build table SQL statement CREATE TABLE set auto-Grow column keyword AutoIncrement use method

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.