Let’s say we have a new table called
id, and other two columns
In most real world scenarios, the primary key column is nothing but positive integers in increasing order for each new row, starting from
1. For example, the first row has
id 1, the next
2, and so on.
So, instead of adding the primary key value manually for each row, this seems like something the database should itself take care of, if needed.
SQL provides an attribute called
AUTOINCREMENT as an addition to the
PRIMARY KEY constraint on a column, which automatically generates the appropriate PRIMARY KEY values. Observe the
CREATE TABLE statement below
To set up the primary key column to be incremented automatically, simply add
AUTOINCREMENT next to the
PRIMARY KEY declaration in the
CREATE TABLE statement.
Observe the set of statements below to see
AUTOINCREMENT in action.
Notice that we haven’t mentioned the value of
id column in any of the insert statements. But, in the results, that column is automatically filled, autoincremented with
ids starting with 1. This is because
id, the PRIMARY KEY column, is marked as