How to Add an Identity to an Existing Column in PL/SQL?

In PL/SQL, adding an identity to an existing column is the basic operation in database management. Identity columns provide an easy way to automatically generate the unique values for each new row inserted into the table. It is served the easy way to make sure each row has a different identifier without the need for manual intervention. This process involves altering the structure of the table to include an identity column and configure its properties.

How to Add an Identity to an Existing Column in PL/SQL

In PL/SQL, adding identity to an existing column involves creating a new column with the identity property, copying data, dropping the old column, and renaming the new one.

The syntax for adding an identity to an existing column in PL/SQL:

ALTER TABLE table_name
ADD column_name GENERATED ALWAYS AS IDENTITY
(START WITH start_value INCREMENT BY increment_value);

Explanation:

• ALTER TABLE table_name is the part of the syntax that specifies the existing table name that you want to alter by adding the identity column.
• ADD column_name is specified as the name of the new column that you are adding to the table.
• GENERATED ALWAYS AS IDENTITY is the clause that defines the new column name properties. GENERATED ALWAYS specifies the values of the column, it will be always generated automatically and it cannot assigned manually. AS IDENTITY specified the column is the column of the identity.
• START WITH start_value describe the starting value for the column of the identity. INCREMENT BY increment_value specified the increment of the value for the identity column. Each row will be inserted into a table having the incremented by value.

Example of how to add an identity to an existing column in PL/SQL

Example 1: Add Identity to an Existing Column, Named SLNO in an Existing Table.

Step 1: Create a table in PL/SQL (Example: emp), and add some columns and rows in it.

Step 2: Retrieve the table data before add the identity to an existing column in PL/SQL using the below syntax.

Select * from table_name;

Example:

Before add an identity to an existing column

Step 3: You can add the identity to an existing column in PL/SQL with the below syntax:

ALTER TABLE table_name
ADD column_name data_type GENERATED ALWAYS AS IDENTITY (START WITH start_value INCREMENT BY increment_value);

Example:

Add an identity to an existing table

Step 4: Verification

If you want to check the is successfully added an identity to an existing column in PL/SQL or not. you can retrieve the table again and check the output. If the column is added you are successfully adding an identity to an existing column in PL/SQL. Otherwise, it is not successful to add an identity to an existing column in PL/SQL.

Output:

Output

Explanation: In the above output, SLNO Identity is successfully added to an existing column in PL/SQL.

Example 2: Add Identity to an Existing Column, Named SLNO in Existing Table.

Step 1: Create a table in PL/SQL (Example: employee), and add some columns and rows in it.

Step 2: Retrieve the table data before add the identity to an existing column in PL/SQL using the below syntax.

Select * from table_name;

Example:

Before add an identity to an existing column

Step 3: You can add the identity to an existing column in PL/SQL with the below syntax:

ALTER TABLE table_name
ADD column_name data_type GENERATED ALWAYS AS IDENTITY (START WITH start_value INCREMENT BY increment_value);

Example:

Add an identity to an existing table

Step 4: Verification

If you want to check the is successfully added an identity to an existing column in PL/SQL or not. you can retrieve the table again and check the output. If the column is added you are successfully add an identity to an existing column in PL/SQL. Otherwise, it is not successful to add an identity to an existing column in PL/SQL.

Output:

Output

Explanation: In the above output, SLNO Identity is successfully added to an existing column in PL/SQL.

Conclusion

In summary, adding an identity to the existing column in the PL/SQL involved altering the table structure to include the column with the identity property, which automatically generated the different values for each row added to the table. Adding the identity to an existing column enhances the efficiency and the integrity of the structure of the database.