Auto-Incrementing Fields in SQL

Auto-incrementing fields automatically generate unique numbers when a new record is added to a table. This is commonly used for primary key columns, ensuring each row has a unique identifier.

Auto-Increment: Definition and Usage

Auto-increment simplifies database management. You don't need to manually assign unique IDs; the database handles it automatically. Each time a new row is inserted, the auto-increment column gets the next available number in the sequence. This is usually an integer and is a very helpful feature when setting up primary keys.

Implementing Auto-Increment

The specific syntax for creating an auto-incrementing field varies depending on the database system:

MySQL: AUTO_INCREMENT

CREATE TABLE Persons (
  Personid INT NOT NULL AUTO_INCREMENT,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  PRIMARY KEY (Personid)
);
      

This makes 'Personid' an auto-incrementing primary key. The default starting value is 1, incrementing by 1 for each new row. You can change the starting value using ALTER TABLE:

ALTER TABLE Persons AUTO_INCREMENT = 100;
      

Inserting a new row (you don't need to specify Personid):

INSERT INTO Persons (FirstName, LastName) VALUES ('Lars', 'Monsen');
      

(A new row is inserted. Personid is automatically assigned.)
      

CREATE TABLE Persons (
  Personid INT IDENTITY(1,1) PRIMARY KEY,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT
);
      

INSERT INTO Persons (FirstName, LastName) VALUES ('Lars', 'Monsen');
      

(A new row is inserted. Personid is automatically assigned.)
      

CREATE TABLE Persons (
  Personid AUTOINCREMENT PRIMARY KEY,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT
);
      

INSERT INTO Persons (FirstName, LastName) VALUES ('Lars', 'Monsen');
      

(A new row is inserted. Personid is automatically assigned.)
      

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
      

INSERT INTO Persons (Personid, FirstName, LastName)
VALUES (seq_person.nextval, 'Lars', 'Monsen');
      

(A new row is inserted. Personid gets the next value from the seq_person sequence.)