In PL/SQL the primary keys are used for the validation of data and for the relation between the tables, for example, the foreign key. This article will explain the fundamentals of primary keys with examples of creating, altering, dropping, enabling, as well as disabling primary key constraints using PL/SQL.
What is a Primary Key in PL/SQL?
- In PL/SQL, the primary key is a column in a table or a set of columns that is used to uniquely determine each record in a table.
- The primary key rule therefore prevents the existence of duplicate or null values in columns chosen as the primary key.
- It also has an added feature of creating an index on the column(s) used in defining the primary key constraint to improve efficiency.
Key Properties of a Primary Key
- Uniqueness: The primary key cannot be duplicated among two different rows.
- Non-Nullable: A primary key column cannot contain null values.
- Indexing: To enhance the speed of the query, the primary key index is set to be created as an identity
Creating a Primary Key Using the CREATE TABLE Statement
When defining a new table, we can specify a primary key directly within the CREATE TABLE statement. This ensures that the column has the primary key, which will enforce uniqueness and prevent null values.
Syntax:
CREATE TABLE table_name (
column_name datatype [CONSTRAINT constraint_name] PRIMARY KEY
);
Example:
CREATE TABLE Employees (
Employee_ID NUMBER PRIMARY KEY,
First_Name VARCHAR2(50),
Last_Name VARCHAR2(50),
Department_ID NUMBER
);
Output:
Table EMPLOYEES created.
Explanation:
In this example, the Employee_ID column is defined as the primary key for the Employees table. This ensures that each employee will have a unique Employee_ID and that no record can have a NULL value in this column.
Creating a Primary Key Using the ALTER TABLE Statement
If we have to add a primary key constraint to the already existing table, we may use ALTER TABLE statement. This enables one to alter the table structure so as to have primary key constraints once the table has been created.
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
Example:
ALTER TABLE Employees
ADD CONSTRAINT pk_Employee PRIMARY KEY (Employee_ID);
Output:
Table EMPLOYEES altered.
Explanation:
In this example, it explains how to add a primary key constraint named pk_Employee to the Employee_ID column of the Employees table even after the table has been created.
Primary key constraint is applied on the table structure with the help of ALTER TABLE statement.
Dropping a Primary Key
Suppose there is a need to drop a primary key constraint on a table, then the SQL statement to use is ALTER TABLE with the DROP CONSTRAINT option. This action will help in disabling the enforcement of the primary key on the specified column.
Example:
ALTER TABLE Employees
DROP CONSTRAINT pk_Employee;
Output:
Table EMPLOYEES altered.
The primary key constraint pk_Employee is dropped from the Employee_ID column.
Disabling a Primary Key
Suppose we need to temporarily disable a primary key constraint, during data migrations or bulk updates. The DISABLE CONSTRAINT clause in the ALTER TABLE statement allows us to deactivate the primary key constraint.
Example:
ALTER TABLE Employees
DISABLE CONSTRAINT pk_Employee;
Output:
Table EMPLOYEES altered.
The primary key constraint pk_Employee is disabled.
Enabling a Primary Key
Suppose that we want to enable an existing primary key constraint that was disabled for some reason. This is done using the ENABLE CONSTRAINT clause in the ALTER TABLE statement.
Example:
ALTER TABLE Employees
ENABLE CONSTRAINT pk_Employee;
Output:
Table EMPLOYEES altered.
The primary key constraint pk_Employee is enabled again.
Conclusion
Managing primary keys is a crucial aspect of database administration in PL/SQL. By understanding how to create, modify, drop, disable, and enable primary keys, you can ensure the integrity and proper functioning of your database tables. These operations help maintain data accuracy and support the logical structure of your database design.
Similar Reads
MySQL Primary Key
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format and to uniquely identify each record in a table, we require a Primary Key. In this article, we will learn how to add, modify, and remove t
4 min read
PostgreSQL - Primary Key
A primary key in PostgreSQL is a column (or a set of columns) that uniquely identifies each row in a table. It is an important component for ensuring data integrity and plays an important role in database normalization. When we define a primary key in PostgreSQL, the database automatically creates a
4 min read
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint in SQL is one of the most important constraints used to ensure data integrity in a database table. A primary key uniquely identifies each record in a table, preventing duplicate or NULL values in the specified column(s). Understanding how to properly implement and use the
5 min read
MariaDB Primary Key
Primary keys are important in the field of relational databaseÂs. They keep the data unique and aid in easy data fetching. This article is about understanding the primary keys in MariaDB. MariaDB is a wideÂly used open-source systeÂm for managing relational databases. The MariaDB database is used
5 min read
SQL Query to Remove Primary Key
Removing a primary key constraint from a table in SQL Server involves several steps to ensure the database schema is correctly updated. A primary key constraint uniquely identifies each record in a table and removing it can impact data integrity and relationships. In this article, we will see how to
3 min read
Security in PL/SQL
PL/SQL security is that feature of the Oracle database management where protection of the data is ensured along with proper application interaction with the database. It refers to access control, user privilege administration and secure coding against SQL injection, unauthorized accessing of the dat
7 min read
Primary Key in DBMS
In DBMS there are different types of keys available that are used for various purposes, for which the most common key is known as a primary key. A primary key is a unique identifier assigned to each record within a database table. A primary key in a table that uniquely identifies each row and column
8 min read
How to Reset Primary Key Sequence in PL/SQL
Resetting a PL/SQL primary key sequence is an important task to ensure that the sequence values align with the current state of the table. When a sequence falls out of sync with the data in a table, it can lead to issues where new records receive primary key values that conflict with existing data.I
4 min read
Procedures in PL/SQL
PL/SQL procedures are reusable code blocks that perform specific actions or logic within a database environment. They consist of two main components such as the procedure header which defines the procedure name and optional parameters and the procedure body which contains the executable statements i
5 min read
PL/SQL Unique Key
In Oracle databases, ensuring data integrity and uniqueness within the table is a crucial aspect of the database design. One key feature that assists in this is the Unique Key Constraint. In this article, we will learn about Unique Key in PL/SQL, including its types, syntax, use cases, and examples.
6 min read