W3Schools Learner's Blog

w3Schools programming knowledge summary website

div

1/03/2018

SQL AUTO INCREMENT Field

AUTO INCREMENT fields are used for auto generating values for particular column whenever new row is being inserted.
Very often the primary key of a table needs to be created automatically; we define that field as AUTO INCREMENT field.
Syntax for the AUTO INCREMENT field is:
column_name data_type constraint AUTOINCREMENT;
column_name - is the name of the column (usually primary key)
data_type - is the type of data column is storing
constraint – is the constraint definition (if any)
The AUTOINCREMENT keyword is termed by other keywords in other database systems

MySQL: AUTO_INCREMENT

column_name data_type constraint AUTO_INCREMENT;

MySQL: AUTO_INCREMENT Example

CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
.
.
)

Default starting value of AUTO_INCREMENT is 0

To modify it alter value as in below example.
ALTER TABLE Employee AUTO_INCREMENT=10000

SQL SERVER: PRIMARY KEY IDENTITY

column_name data_type constraint IDENTITY;

SQL SERVER: Example

CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY IDENTITY,
Name VARCHAR(100) NOT NULL,
.
.
)
The default starting value of IDENTITY is 1 and will increment by 1 for each record. To modify it alter value as in below example.
EmployeeID INT PRIMARY KEY IDENTITY(1000,2)

SQL SERVER: ORACLE: Sequence

Oracle uses different approach for generating unique identifiers. An auto incremented filed with SEQUENCE object is created first and this value is assigned to table’s column
CREATE SEQUENCE sequence_name
START WITH start_value
INCREMENT BY increment_value
INSERT INTO table_name(autoincrement_column, column1, column2, ..)
VALUES(sequence_name.nextval, value1, value2, ..)
sequence_name - is the sequence to be created
start_value – is the start value of identifier
increment_value – is the increment value to which previous value should be incremented
table_name – is the table name on which auto increment value is needed
autoincrement_column– is the column whose value to be auto generated

SQL SERVER: ORACLE Example

CREATE SEQUENCE sequence_employee
START WITH 1
INCREMENT BY 1

INSERT INTO Employee(EmployeeID, Name, ..)
VALUES(sequence_employee.nextval, “Test”, ..)

MS ACCESS: AUTO INCREMENT

column_name data_type constraint AUTOINCREMENT;
Example: MS AUTO INCREMENT
CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(100) NOT NULL,
.
.
)
The default starting value of AUTOINCREMENT is 1 and will increment by 1 for each record. To modify it alter the value as in example below.
EmployeeID INT PRIMARY KEY AUTOINCREMENT(1000,2)

No comments:

Post a Comment

Note: only a member of this blog may post a comment.