AUTO INCREMENT column (SQL AUTO INCREMENT column)

 

The AUTO INCREMENT field will automatically increment the value of the column, because each time you add data, the field value will be automatically incremented. That is to say, the AUTO INCREMENT field value will be unique. The purpose of this field is like an identification code or a stream. Number, and AUTO INCREMENT is often used together with Primary Key.

Set AUTO INCREMENT field

MySQL

CREATE TABLE customers (
  C_Id INT AUTO_INCREMENT,
  Name varchar(50),
  Address varchar(255),
  Phone varchar(20),
  PRIMARY KEY (C_Id)
);

MySQL syntax uses the keyword AUTO_INCREMENT. Note that the AUTO_INCREMENT field should be designated as PRIMARY KEY, otherwise there will be errors!

Add a piece of data:

INSERT INTO customers (Name, Address, Phone)
VALUES ('NameXXX', 'AddressXXX', 'PhoneXXX');

The C_Id field does not need to specify a value, and MySQL will automatically increment from 1 column by column by default (2, 3, 4...). However, you can also specify an initial value for the AUTO_INCREMENT field, the syntax is as follows:

ALTER TABLE table_name AUTO_INCREMENT=Starting number;

SQL Server

CREATE TABLE customers (
  C_Id INT IDENTITY PRIMARY KEY,
  Name varchar(50),
  Address varchar(255),
  Phone varchar(20)
);

SQL Server syntax uses the keyword IDENTITY.

Add a piece of data:

INSERT INTO customers (Name, Address, Phone)
VALUES ('NameXXX', 'AddressXXX', 'PhoneXXX');

SQL Server will automatically increment column by column starting from 1 by default (2, 3, 4...). However, you can also specify a starting value and increment for the IDENTITY field, the syntax is as follows:

將 IDENTITY 改成 IDENTITY(Starting value, increment)

Oracle

Oracle is different, we need to create a Sequence first, and then the increment value is grabbed from this Sequence.

Sequence establishment syntax:

CREATE SEQUENCE sequence_name
START WITH 1     --Set the starting value
INCREMENT BY 1;  --Set increment

Add a piece of data:

INSERT INTO customers (C_Id, Name, Address, Phone)
VALUES (sequence_name.NEXTVAL ,'NameXXX', 'AddressXXX', 'PhoneXXX');

NEXTVAL is used to get the next value.

MS Access

CREATE TABLE customers (
  C_Id INT PRIMARY KEY AUTOINCREMENT,
  Name varchar(50),
  Address varchar(255),
  Phone varchar(20)
);

Access syntax uses the keyword AUTOINCREMENT.

Add a piece of data:

INSERT INTO customers (Name, Address, Phone)
VALUES ('NameXXX', 'AddressXXX', 'PhoneXXX');

By default, Access will automatically increment column by column starting from 1 (2, 3, 4...). However, you can also specify a starting value and increment for the AUTOINCREMENT field, the syntax is as follows:

Change AUTOINCREMENT to AUTOINCREMENT (start value, increment)