The identity column in a table defines a column that generates the value automatically once you define that column as Identity. You do not require to insert any values explicitly for the Identity column. Teradata itself takes care of the value once you define it.
In Teradata, you cannot guarantee that the number generated by the Identity column will always be in sequence. This is because the numbers are generated on an AMP-Local basis due to performance reasons.
Teradata Identity Column Syntax
The syntax for defining the Identity column is as below.
column_name INTEGER GENERATED always as IDENTITY (START WITH value1 INCREMENT BY value2 MINVALUE value3 MAXVALUE value4 NO CYCLE)
Teradata Identity Column Example
Lets first, create a students table where roll_no will be Identity.
CREATE TABLE Teradatapoint.students ( roll_no INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 99999 NO CYCLE), first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender CHAR(1) NOT NULL );
Now, insert few records into the table as below.
INSERT INTO Teradatapoint.students (first_name, last_name,gender) VALUES ('Deepan','Das','M');
INSERT INTO Teradatapoint.students (first_name, last_name,gender) VALUES ('Sagar','Sharma','M');
INSERT INTO Teradatapoint.students (first_name, last_name,gender) VALUES ('Rupa','Das','F');
If you check the data of the table, you will see that roll_no is generated automatically. We haven’t inserted any values for the column roll_no.
SELECT * FROM Teradatapoint.students ORDER BY roll_no; *** Query completed. 3 rows found. 4 columns returned. *** Total elapsed time was 1 second. roll_no first_name last_name gender ----------- -------------------------------------------------- ------------ 1 Deepan Das M 2 Sagar Sharma M 3 Rupa Das Y