Lecture 15
Auto-increment
allows a unique number to be generated when a new record is inserted into a
table.
________________________________________
AUTO INCREMENT a
Field
Very often we
would like the value of the primary key field to be created automatically every
time a new record is inserted.
We would like to
create an auto-increment field in a table.
________________________________________
Syntax for MySQL
The following
SQL statement defines the "P_Id" column to be an auto-increment
primary key field in the "Persons" table:
CREATE TABLE
Persons
(
P_Id int NOT
NULL AUTO_INCREMENT,
LastName
varchar(255) NOT NULL,
FirstName
varchar(255),
Address
varchar(255),
City
varchar(255),
PRIMARY KEY
(P_Id)
)
MySQL uses the
AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the
starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new
record.
To let the
AUTO_INCREMENT sequence start with another value, use the following SQL
statement:
ALTER TABLE
Persons AUTO_INCREMENT=100
To insert a new
record into the "Persons" table, we will not have to specify a value
for the "P_Id" column (a unique value will be added automatically):
INSERT INTO
Persons (FirstName,LastName)
VALUES
('Lars','Monsen')
The SQL
statement above would insert a new record into the "Persons" table.
The "P_Id" column would be assigned a unique value. The
"FirstName" column would be set to "Lars" and the
"LastName" column would be set to "Monsen".
________________________________________
Syntax for SQL
Server
The following
SQL statement defines the "P_Id" column to be an auto-increment
primary key field in the "Persons" table:
CREATE TABLE
Persons
(
P_Id int PRIMARY
KEY IDENTITY,
LastName
varchar(255) NOT NULL,
FirstName
varchar(255),
Address
varchar(255),
City
varchar(255)
)
The MS SQL
Server uses the IDENTITY keyword to perform an auto-increment feature.
By default, the
starting value for IDENTITY is 1, and it will increment by 1 for each new
record.
To specify that
the "P_Id" column should start at value 10 and increment by 5, change
the identity to IDENTITY(10,5).
To insert a new
record into the "Persons" table, we will not have to specify a value
for the "P_Id" column (a unique value will be added automatically):
INSERT INTO
Persons (FirstName,LastName)
VALUES
('Lars','Monsen')
The SQL
statement above would insert a new record into the "Persons" table.
The "P_Id" column would be assigned a unique value. The
"FirstName" column would be set to "Lars" and the
"LastName" column would be set to "Monsen".
________________________________________
Syntax for
Access
The following
SQL statement defines the "P_Id" column to be an auto-increment
primary key field in the "Persons" table:
CREATE TABLE
Persons
(
P_Id PRIMARY KEY
AUTOINCREMENT,
LastName
varchar(255) NOT NULL,
FirstName
varchar(255),
Address
varchar(255),
City
varchar(255)
)
The MS Access
uses the AUTOINCREMENT keyword to perform an auto-increment feature.
By default, the
starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new
record.
To specify that
the "P_Id" column should start at value 10 and increment by 5, change
the autoincrement to AUTOINCREMENT(10,5).
To insert a new
record into the "Persons" table, we will not have to specify a value
for the "P_Id" column (a unique value will be added automatically):
INSERT INTO
Persons (FirstName,LastName)
VALUES
('Lars','Monsen')
The SQL
statement above would insert a new record into the "Persons" table.
The "P_Id" column would be assigned a unique value. The
"FirstName" column would be set to "Lars" and the
"LastName" column would be set to "Monsen".
________________________________________
Syntax for
Oracle
In Oracle the
code is a little bit more tricky.
You will have to
create an auto-increment field with the sequence object (this object generates
a number sequence).
Use the
following CREATE SEQUENCE syntax:
CREATE SEQUENCE
seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
The code above
creates a sequence object called seq_person, that starts with 1 and will
increment by 1. It will also cache up to 10 values for performance. The cache
option specifies how many sequence values will be stored in memory for faster
access.
To insert a new
record into the "Persons" table, we will have to use the nextval
function (this function retrieves the next value from seq_person sequence):
INSERT INTO
Persons (P_Id,FirstName,LastName)
VALUES
(seq_person.nextval,'Lars','Monsen')
The SQL
statement above would insert a new record into the "Persons" table.
The "P_Id" column would be assigned the next number from the
seq_person sequence. The "FirstName" column would be set to
"Lars" and the "LastName" column would be set to
"Monsen".
_______________________________________
No comments:
Post a Comment