Auto Increment Column in Teradata

We can create auto increment column in Teradata which insert the value in the column automatically.

Syntax is:-

CREATE SET || MULTISET TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE NULL || NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH [INTEGER], INCREMENT BY [INTEGER], MAXVALUE [INTEGER]),
COLUMN_NAME DATA_TYPE 
COLUMN_NAME DATA_TYPE,
....
);

While creating IDENTITY column, make sure you have specified number families data type like int, number, decimal etc.

Here:-

START WITH is the value from which we need to start to insert into the table.

INCREMENT BY is the value by which we need to increment the value of the column.

MAXVALUE is the value until sequence will keep generating the value.


Let's have an example:-

CREATE MULTISET TABLE EMPLOYEE
(
EMPNO INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, MAXVALUE 999999999999),
EMPNAME VARCHAR(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);

Read Also:-  Auto Increment Column in MySql