SQL Alternate Key

Introduction

In this chapter, we will learn about the ALTERNATE KEY in SQL. The concept of an alternate key is important in database design for identifying unique columns other than the primary key. This chapter will cover the definition, usage, and examples to help you understand how to use alternate keys effectively.

What is an Alternate Key?

An alternate key is any candidate key that is not chosen as the primary key in a database table. A candidate key is a column, or a set of columns, that can uniquely identify any record in the database table. When one of these candidate keys is chosen as the primary key, the other candidate keys are referred to as alternate keys.

Characteristics of an Alternate Key

1. Uniqueness: Each value in the alternate key must be unique.
2. Not Null: Alternate keys must not contain NULL values.
3. Alternate to Primary Key: They are not the primary key but can be used as a unique identifier.

Syntax for Defining an Alternate Key

Basic Syntax

To define an alternate key, you typically use the UNIQUE constraint. Here's the syntax to define an alternate key:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype UNIQUE,
    column3 datatype,
    ...
);

Step-by-Step Example

1. Create a Sample Table with an Alternate Key

First, we will create a sample table named employees with a primary key and an alternate key.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_number VARCHAR(50) UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10, 2)
);

In this example, the id column is the primary key, and the employee_number column is the alternate key.

2. Insert Sample Data

INSERT INTO employees (employee_number, first_name, last_name, email, salary)
VALUES
('E001', 'Ramesh', 'Kumar', 'ramesh.kumar@example.com', 50000),
('E002', 'Sita', 'Patel', 'sita.patel@example.com', 60000),
('E003', 'Arjun', 'Singh', 'arjun.singh@example.com', 55000),
('E004', 'Priya', 'Sharma', 'priya.sharma@example.com', 62000);

3. Verify the Alternate Key Constraint

To verify that the employee_number column is unique, try to insert a duplicate value:

INSERT INTO employees (employee_number, first_name, last_name, email, salary)
VALUES ('E001', 'Ravi', 'Verma', 'ravi.verma@example.com', 70000);

This command will result in an error because the employee_number value 'E001' already exists in the table, demonstrating that the UNIQUE constraint enforces unique values.

Adding an Alternate Key to an Existing Table

If you have an existing table and want to add an alternate key, you can use the ALTER TABLE statement.

Example

Assume we have an existing table named employees without a UNIQUE constraint on the email column:

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

Verify the Addition of the Alternate Key

To verify the addition of the UNIQUE constraint, try to insert a duplicate email:

INSERT INTO employees (employee_number, first_name, last_name, email, salary)
VALUES ('E005', 'Ravi', 'Verma', 'ramesh.kumar@example.com', 70000);

This command will result in an error because the email value 'ramesh.kumar@example.com' already exists in the table, demonstrating that the UNIQUE constraint enforces unique values.

Conclusion

An ALTERNATE KEY is a crucial concept in database design for ensuring data integrity and uniqueness in columns other than the primary key. This chapter covered the definition, characteristics, and syntax for defining alternate keys, as well as examples to illustrate their usage. Understanding how to define and use alternate keys effectively will enhance your ability to design robust and reliable database schemas.