How to Add an Identity to an Existing Column in PL/SQL?
Last Updated :
12 Apr, 2024
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 columnStep 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 tableStep 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:
OutputExplanation: 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 columnStep 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 tableStep 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:
OutputExplanation: 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.
Similar Reads
How to Add an Identity to an Existing Column in MySQL?
Adding an identity (auto-increment) property to an existing column in MySQL is a common task when you want to assign unique values automatically. This feature is particularly useful for maintaining unique identifiers in a table. In this guide, we will explore the syntax, and usage, and provide examp
3 min read
How to Add an Identity to an Existing Column in SQLite
An identity column as a column added to an existing table in SQLite would probably be a crucial task while database restructuring or when implementing new features. The identity column is provided with a compulsory key with auto-incremented values, which makes the administration of data easier and a
5 min read
How to Add an IDENTITY to an Existing Column in SQL Server
It enables you to store, organize, and manipulate data in a relational format, meaning data is organized into tables. It Stores and manages data for dynamic web applications, ensuring effective user experiences. In this article, we will learn about How to add an identity to an existing column in SQL
5 min read
How to Add an Identity to an Existing Column in PostgreSQL?
PostgreSQL, a robust open-source relational database management system, offers a variety of tools for managing and organizing data. One such feature is the ability to add an identity to an existing column, which is particularly useful in situations when each row requires a unique identifier. In this
4 min read
How to Add a Boolean Datatype Column to an Existing Table in SQL?
In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1. This will provide the boolean nature for a data type. Regarding the
4 min read
How to Add Column in View in PL/SQL?
In Oracle PL/SQL, adding a column to a view is not done through the ALTER VIEW command since it does not directly support adding columns. Instead, views are modified by recreating them using the CREATE OR REPLACE VIEW statement, which allows the addition of new columns while retaining the existing s
3 min read
How to Check if a Column Exists in a SQL Server Table?
In this article, we will look at how to check if a particular column exists in a database table or not. For checking the existence of a column we need to create the table first. So, let us create a table with some columns and data. Creating table: Syntax: CREATE TABLE table_name ( column1 datatype,
2 min read
SQL Query to Add a New Column After an Existing Column in SQL
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve data from relational databases like MySQL, Oracle, SQL Server, Postgres, etc. In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command
3 min read
How to fix Cannot Insert Explicit Value For Identity Column in Table in SQL
In SQL, encountering the error message "Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF" is a common challenge faced by developers when managing identity columns. Identity columns are a powerful feature that automatically generate unique values, o
8 min read
How To Reset Identity Column Values In SQL
An Identity Column in SQL is an auto-incrementing column used to generate unique values for each row inserted into a table. This feature is commonly used in tables that require a unique identifier, such as primary keys. The identity column allows the database to automatically handle the generation o
5 min read