AUTO_INCREMENT SQL

AUTO_INCREMENT is generally used in conjunction with CREATE TABLE. It ensures that the data in a specific column for each successive INSERTed row is increased from the previous INSERTed row by 1. This is handy when applied to the PRIMARY KEY which requires unique data within its cell and is often made the identifier for each row. The syntax:

CREATE TABLE table(
table_id IN NOT NULL AUTO_INCREMENT,
column2 varchar(255) NOT NULL,
PRIMARY KEY (table_id)
)

The starting value, by default, is 1. A value other than 1 can be spcified with AUTO_INCREMENT=number. AUTO_INCREMENT may be added later by using the ALTER TABLE statement in the following manner:

ALTER TABLE table AUTO_INCREMENT=1000

This can only be used to change a row that is already designated as AUTO_INCREMENT and the number must be higher than the highest number already used in the table for that column.

AUTO_INCREMENT works for mysql. Other SQL server applications use varying keywords, AUTOINCREMENT in Access, IDENTITY in others, and Oracle has a separate syntax all its own.

In Oracle a SEQUENCE is created as an object itself using the following syntax:

CREATE SEQUENCE sequence_name
MINVALUE number
START WITH number
INCREMENT BY number
CACHE number

The advantage here is that it is more nuanced than other server methods. However, it is also more cumbersome, especially when INSERTing a new row. In this case the nextval function must be called to get the next sequential value.

INSERT INTO table (table_id, column2, column3)
VALUES (seq_name.nextval,'input2','input3')

< AND Operator | BETWEEN Operator SQL >


Send notes in disappearing ink!

Interesting Pages