SQL Auto Increment How to Use It in Different Databases

转载：SQL Auto Increment: How to Use It in Different Databases - Database Star

SQL auto-increment is a feature that allows you to generate a unique number when inserting rows.

Learn how to set this up in SQL Server, MySQL, PostgreSQL, and Oracle in this article.

# What is SQL Auto Increment?

Autoincrement is a feature of a database that lets you generate a unique number automatically whenever a new row is inserted into a table.

Have you ever worked with data in a table and realised you want each row to have a unique ID, but don’t really mind what the ID value is? This is often the case for primary keys.

Rather than using a spreadsheet or generating a random number, you can use the auto-increment feature of the database to do this. Using this feature is fast, consistent, and pretty simple.

The way to do it slightly differs between databases:

Let’s take a look at some examples.

# SQL Server Auto Increment

Autoincrement is best implemented by specifying it when you create a table.

This example shows the creation of a product table using an auto-increment column as the primary key.

1
2
3
4
5

CREATE TABLE product (
  product_id INT IDENTITY (1, 1) PRIMARY KEY,
  product_name VARCHAR(200),
  price INT
);

Microsoft SQL Server uses the IDENTITY keyword to define an auto-increment for a column. The IDENTITY keyword is used when defining a column and is placed after the data type.

In this example, we have specified IDENTITY(1, 1). The first parameter defines the starting value, so the first value used is 1. The second parameter defines what the increment is for each new row, which is also 1.

So, the first row will have a value of 1, the second row has a value of 2, the third row is 3, and so on.

You can change the starting value and increment if required. For example, to specify a column starts at 100 and increments by 5:

product_id INT IDENTITY (100, 5) PRIMARY KEY

Now how do we insert a new value for this column? We run an INSERT statement without specifying a value for the column in the statement.

For example:

1
2

INSERT INTO product (product_name, price)
VALUES ('Desk chair', 50);

This value is inserted. We did not specify the product_id, and the database will generate a value for it.

This is what the table looks like:

If we insert a second row, the ID is auto-incremented:

1
2

INSERT INTO product (product_name, price)
VALUES ('Lounge', 200);

We can see that the SQL Server auto-increment feature is populating the product_id column with new values.

If you ever need to reset this value, you can follow the steps in this guide: How to Reset IDENTITY Column Values in SQL Server

# MySQL Auto Increment

This example for MySQL shows you how to create a table and specify a column as auto-increment:

1
2
3
4
5

CREATE TABLE product (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(200),
  price INT
);

Notice that the keyword has an underscore in it. I often forget this. It’s AUTO_INCREMENT.

By default, the column values start at 1 and increment by 1 each time.

To insert a record into the table, and have it auto-incremented, simply insert the record without specifying the auto-increment column.

For example:

1
2

INSERT INTO product (product_name, price)
VALUES ('Desk chair', 50);

This value is inserted. We did not specify the product_id, and the database will generate a value for it.

This is what the table looks like:

If we insert a second row, the ID is auto-incremented:

1
2

INSERT INTO product (product_name, price)
VALUES ('Lounge', 200);

We can see that the MySQL auto-increment feature is populating the product_id column with new values.

If you want to start the auto_increment value at any other number, you can use the ALTER TABLE statement to change the value.

For example to change it to 100:

1

ALTER TABLE product SET AUTO_INCREMENT=100;

# PostgreSQL Auto Increment

There are three ways to auto-increment a value in PostgreSQL: a serial data type, a sequence, and an identity column.

# Using the Serial Data Type

The SERIAL data type in PostgreSQL can be used to create an auto-increment column. Behind the scenes, this creates a sequence object, which is an object that is used to generate auto-increment values.

Here’s an example:

1
2
3
4
5

CREATE TABLE product (
  product_id INT SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  price INT
);

Behind the scenes, a SERIAL data type does a few things:

1. It creates a sequence
2. It adds a NOT NULL constraint to the column
3. It assigns the owner of the sequence to the column you specified (in this example it’s product_id), so if you drop the column or table the sequence is dropped.

Now how do we insert a new value for this column? We run an INSERT statement without specifying a value for the column in the statement.

For example:

1
2

INSERT INTO product (product_name, price)
VALUES ('Desk chair', 50);

This value is inserted. We did not specify the product_id, and the database will generate a value for it.

This is what the table looks like:

If we insert a second row, the ID is auto-incremented:

1
2

INSERT INTO product (product_name, price)
VALUES ('Lounge', 200);

We can see that the PostgreSQL auto-increment feature is populating the product_id column with new values.

If you want to get the name of a sequence from a serial column, you can use the pg_get_serial_sequence function:

1

SELECT pg_get_serial_sequence('product', 'product_id');

You can get the current value of the sequence by providing the name to the currval function:

1

SELECT currval(pg_get_serial_sequence('product', 'product_id'));

There are actually three types of SERIAL values, with different storage sizes and lengths:

# Using a Sequence

Another way to use auto increment in PostgreSQL is to use a sequence object. This is similar to SERIAL, as SERIAL just generates a sequence behind the scenes anyway.

You may want to use a sequence if you want more control over the starting value and increment value.

Here’s how you use a sequence. First, create the sequence:

1
2
3

CREATE SEQUENCE prod_sequence
START 1
INCREMENT 1;

You can change the start and increment values to other numbers when you run this statement if you need to.

Now, when we insert a new record into the product table, we use the next value of the sequence by calling nextval(‘prod_sequence’) as our product_id:

1
2

INSERT INTO product (product_id, product_name, price)
VALUES (nextval('prod_sequence), 'Desk chair', 50);

The new record is inserted into the table.

Inserting a second record will auto-increment the column:

1
2

INSERT INTO product (product_id, product_name, price)
VALUES (nextval('prod_sequence), Lounge', 200);

Just like with the SERIAL method, you can get the current value by using the currval function on the sequence.

1

SELECT currval('prod_sequence');

# Using an Identity Column

In PostgreSQL 10, an IDENTITY feature was introduced. It works similar to the SERIAL method above.

Here’s how to use it:

1
2
3
4
5

CREATE TABLE product (
  product_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(200),
  price INT
);

The part of the statement that defines the auto-increment is:

1

GENERATED BY DEFAULT AS IDENTITY

Add this to the column and it’s now an identity column.

Now, to use the auto-increment identity feature, simply insert a new row without specifying a value for the column.

For example:

1
2

INSERT INTO product (product_name, price)
VALUES ('Desk chair', 50);

This value is inserted. We did not specify the product_id, and the database will generate a value for it.

This is what the table looks like:

If we insert a second row, the ID is auto-incremented:

1
2

INSERT INTO product (product_name, price)
VALUES ('Lounge', 200);

We can see that the PostgreSQL IDENTITY feature is populating the product_id column with new values.

# PostgreSQL SERIAL vs IDENTITY

Which method would you use for your auto-increment columns: SERIAL or IDENTITY?

The IDENTITY method is recommended as it conforms to the SQL standard. Even though it looks similar, it follows the standard and therefore makes it easier for others to understand and easier if you ever need to migrate databases.

It also doesn’t have issues with permissions. SERIAL columns generate a sequence owned by the column. This can cause issues with deployments and testing.

Using IDENTITY is also one less object to manage.

# Oracle Auto Increment

There are two ways to create an auto-increment field in Oracle.

# Sequence

We have to create a SEQUENCE object. This works similar to one of the PostgreSQL methods, but Oracle doesn’t have an identity feature you can simply add to the column.

I’ve written about sequences before, but here’s a simple example of how to create one:

1

CREATE SEQUENCE prod_sequence;

This will use a default starting value of 1 and increment by 1.

You can also specify these values or use different values when creating the sequence:

1
2
3
4

CREATE SEQUENCE prod_sequence
MINVALUE 1
START WITH 1
INCREMENT BY 1;

To use these values as your auto-increment values in a table, simply insert the record and specify the nextval attribute of the sequence.

For example:

1
2

INSERT INTO product (product_id, product_name, price)
VALUES (prod_sequence.nextval, 'Desk chair', 50);

The new record is inserted into the table.

Inserting a second record will auto-increment the column:

1
2

INSERT INTO product (product_id, product_name, price)
VALUES (prod_sequence.nextval, Lounge', 200);

You can get the current value of a sequence by calling sequence.currval.

1
2

SELECT prod_sequence.currval
FROM dual;

# Identity Column

Identity columns were introduced into Oracle in 12c, which was a few years ago now.

To use them, you simply add a few keywords to the end of your column definition when creating a table.

1
2
3
4
5

CREATE TABLE product (
  product_id NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY,
  product_name VARCHAR2(200),
  price INT
);

The part of the statement that defines the auto-increment is:

1

GENERATED BY DEFAULT ON NULL AS IDENTITY

This statement ensures this is now an identity column, and will have values automatically generated for it.

# Conclusion

So that’s how you can create an SQL auto-increment column. SQL Server uses IDENTITY, MySQL uses AUTO_INCREMENT, PostgreSQL can use IDENTITY or a sequence, and Oracle uses a sequence.

# Comments

# SQLite Auto Increment

1
2
3
4
5

CREATE TABLE product (
  product_id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name VARCHAR(200),
  price INT
);