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.

Unique Constraint in Oracle

A unique constraint is a rule applied to the column or a set of columns in the database table that ensures that all values in the specified column(s) are distinct from each other. Unlike a primary key, a unique constraint allows the NULL values provided that all non-NULL values are exceptional.

Difference Between a Unique Constraint and a Primary Key

Example 1: Create a Unique Constraint Using the CREATE TABLE Statement

When creating a new table, a unique constraint can be applied to specific columns to ensure that no duplicate values are allowed in that column. In this case, we are applying the unique constraint on the emp_email column, ensuring that no two employees can have the same email address.

Employees Table

The CREATE TABLE statement defines the employees table with four columns: emp_id, emp_name, emp_email, and emp_phone. The emp_id column serves as the primary key, uniquely identifying each employee, ensuring that no two records have the same emp_id.

The emp_name column stores the name of the employee, while the emp_email column has a unique constraint, preventing duplicate email addresses across employee records. Finally, the emp_phone column holds the employee's phone number.

The INSERT INTO statements are used to add employee data, specifying values for emp_id, emp_name, emp_email, and emp_phone to populate the table.

Query:

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_email VARCHAR2(100) UNIQUE,  -- Unique constraint on emp_email
    emp_phone VARCHAR2(20),
    PRIMARY KEY (emp_id)
);

-- Inserting employee records into the employees table
INSERT INTO employees (emp_id, emp_name, emp_email, emp_phone) 
VALUES (101, 'John Doe', '[email protected]', '123-456-7890');

INSERT INTO employees (emp_id, emp_name, emp_email, emp_phone) 
VALUES (102, 'Jane Smith', '[email protected]', '234-567-8901');

INSERT INTO employees (emp_id, emp_name, emp_email, emp_phone) 
VALUES (103, 'Alice Johnson', '[email protected]', '345-678-9012');

INSERT INTO employees (emp_id, emp_name, emp_email, emp_phone) 
VALUES (104, 'Bob Brown', '[email protected]', '456-789-0123');

Output:

Explanation:

The output is a table displaying the data stored in the employees table after the insertions:

• Each row contains a unique employee, identified by their emp_id.
  
• The emp_email is unique for each employee, ensuring no duplicates exist in the column due to the unique constraint.
  
• The data successfully demonstrates that the constraints are working, and the table now holds valid employee information with no conflicts.

Example 2: Create a Unique Constraint Using an ALTER TABLE Statement

This ALTER TABLE statement adds a unique constraint named emp_email_uk to the emp_email column of the employees table.

The constraint ensures that no two employees in the table can have the same email address, maintaining data integrity by preventing duplicate entries in the emp_email column.

Query:

ALTER TABLE employees
ADD CONSTRAINT emp_email_uk UNIQUE (emp_email);

Output:

Explanation:

• The ALTER TABLE query adds a unique constraint to the emp_email column, ensuring that no two records in the table will have the same email address.
  
• After this query, trying to insert a record with a duplicate emp_email would result in an error.

Drop Unique Constraint

This ALTER TABLE command is used to remove a unique constraint named emp_email_uk from the employees table. The unique constraint ensures that values in the specified column(s) are unique across all rows in the table.

By dropping this constraint, you allow duplicate values in the column(s) previously constrained by emp_email_uk.

Query:

ALTER TABLE employees
DROP CONSTRAINT emp_email_uk;

Output:

Explanation:

When you execute the ALTER TABLE statement to drop a unique constraint, the output is typically a confirmation message rather than a tabular result.

However, if you want to see the impact in terms of the table’s state before and after dropping the constraint, As you can see in the output table.

Disable Unique Constraint

To temporarily disable a unique constraint we can use the ALTER TABLE statement with the DISABLE CONSTRAINT clause.

The ALTER TABLE command does not produce a visible result set. It simply removes the unique constraint from the emp_email column of the employees table. The table structure after executing this query is shown in the output:

Query:

ALTER TABLE employees
DISABLE CONSTRAINT emp_email_uk;

Output:

Explanation:

• The ALTER TABLE statement with DROP CONSTRAINT removes the unique constraint named emp_email_uk from the employees table.
  
• This action allows duplicate values to be inserted into the emp_email column, as the previous uniqueness enforcement is no longer in effect.
  
• After dropping the constraint, the table structure will still be intact, but without the restriction on unique email addresses. Thus, multiple records with the same emp_email value can now be added.

Enable Unique Constraint

To re-enable a previously disabled unique constraint use the ALTER TABLE statement with ENABLE CONSTRAINT clause. After executing the command to re-enable the unique constraint, the employees table remains structurally the same but now enforces the uniqueness of the emp_email column again.

If the table contains any duplicate email values, the constraint will be enforced and may cause errors if duplicates are present.

Query:

ALTER TABLE employees
ENABLE CONSTRAINT emp_email_uk;

Output:

Explanation:

• The ALTER TABLE statement with ENABLE CONSTRAINT reactivates the unique constraint emp_email_uk on the emp_email column of the employees table.
  
• This ensures that all values in the emp_email column must be unique. Any future inserts or updates to this column must now adhere to this constraint.
  
• If there are existing duplicate values, you will need to resolve them before enabling the constraint, or the system will raise an error when attempting to re-enable it.

Conclusion

The Unique Key constraint in PL/SQL is an essential feature for the maintaining data integrity by the ensuring the uniqueness of the values in the specific columns. Understanding how to create, manage and utilize Unique Keys will help we design robust and reliable database applications.

Whether we're working with the new tables or existing ones mastering Unique Keys is a valuable skill in the PL/SQL development.