PL/SQL Alternate Key

In relational databases, alternate keys play a crucial role in maintaining data integrity by ensuring that columns not designated as the primary key still enforce uniqueness. In this article, we will learn about OUTER JOIN in PL/SQL, including its types, syntax, use cases, and examples.

PL/SQL Alternate Key

• An alternate key is a column or a set of columns that can uniquely identify a row in a table, but which is not selected as the primary key.
  
• It serves as an alternative to the primary key and still enforces uniqueness in the table.
  
• If a table has multiple unique columns, only one is designated as the primary key, while the others become alternate keys.

Key Points

• Uniqueness: An alternate key uniquely identifies rows in the table.
  
• Not Primary Key: It is not selected as the primary key but still enforces uniqueness.
  
• Multiple Keys: A table can have multiple alternate keys but only one primary key.

Example of Alternate Key in PL/SQL

In PL/SQL, alternate keys are enforced by creating unique constraints on the respective columns. A unique constraint ensures that the column's values remain unique across all records, just like a primary key, but without designating it as the primary key.

Employees Table

Consider a table named EMPLOYEES. The table contains the following columns: EMP_ID (Employee ID) – Primary Key EMP_EMAIL (Employee Email) – Alternate Key EMP_SSN (Social Security Number) – Alternate Key In this case, EMP_ID is designated as the primary key, while EMP_EMAIL and EMP_SSN are alternate keys, as they can also uniquely identify an employee but are not chosen as the primary key.

Query:

CREATE TABLE EMPLOYEES (   
EMP_ID NUMBER PRIMARY KEY,   
EMP_NAME VARCHAR2(100),   
EMP_EMAIL VARCHAR2(100),   
EMP_SSN VARCHAR2(11),   
CONSTRAINT UK_EMP_EMAIL UNIQUE (EMP_EMAIL),   
CONSTRAINT UK_EMP_SSN UNIQUE (EMP_SSN)
 );

INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_EMAIL, EMP_SSN)
VALUES (1, 'Alice', '[email protected]', '123-45-6789');
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_EMAIL, EMP_SSN)
VALUES (2, 'Bob', '[email protected]', '987-65-4321');
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_EMAIL, EMP_SSN)
VALUES (3, 'Charlie', '[email protected]', '456-78-1234');

Output:

Explanation:

EMP_ID is the primary key and uniquely identifies each employee. EMP_EMAIL and EMP_SSN have unique constraints, meaning each email and SSN must be unique across all rows, ensuring that these fields act as alternate keys.

Use Cases for Alternate Keys

Alternate keys are useful in several scenarios where multiple columns can uniquely identify a record but only one is chosen as the primary key:

• Data Integrity: Alternate keys ensure that specific fields (like email addresses or social security numbers) remain unique within the table.
  
• Complex Queries: In some queries, you may want to refer to rows by their alternate key rather than the primary key.
  
• Relational Integrity: Alternate keys can be referenced by foreign keys in other tables, ensuring relationships are maintained across tables.

For example, an EMPLOYEES table might have an alternate key on the EMP_EMAIL column, ensuring that no two employees have the same email address.

Retrieving Data Using Alternate Keys

Retrieving data using alternate keys involves querying the database based on columns that are not the primary key but still uniquely identify each record. '

For example, in a table where EMP_EMAIL is an alternate key, you can retrieve an employee's details using their email address instead of the primary key. This allows flexibility in accessing data while maintaining the integrity and uniqueness of the information.

Conclusion

Alternate keys in PL/SQL are crucial for maintaining data integrity, especially when multiple fields can uniquely identify records. By defining alternate keys with UNIQUE constraints, databases can enforce uniqueness beyond the primary key, ensuring consistent and reliable data across the system.