MySQL Alternate Key

Introduction

In this chapter, we will learn about the alternate key in MySQL. An alternate key is any candidate key that is not chosen as the primary key of a table. These keys are unique and can be used to identify rows uniquely, just like the primary key. The term "alternate key" is often used in database design to indicate these additional unique keys. We will cover the concept, syntax for defining alternate keys, examples, and important considerations for using alternate keys in MySQL.

Concept of Alternate Key

In database design, multiple candidate keys can uniquely identify rows in a table. One of these candidate keys is chosen as the primary key, and the rest are known as alternate keys. These keys are essential for ensuring data integrity and can be used in queries to identify specific rows.

Syntax

The basic syntax for defining an alternate key in MySQL is:

When creating a table:

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

Adding an alternate key to an existing table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (alternate_key_column);

Example

Let’s go through an example where we create a table with an alternate key and then add an alternate key to another table.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with an Alternate Key:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    employee_code VARCHAR(10) UNIQUE,
    department_id INT
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, employee_code, department_id) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', 'E001', 1),
('Priya', 'Singh', 'priya.singh@example.com', 'E002', 2),
('Amit', 'Kumar', 'amit.kumar@example.com', 'E003', 1),
('Neha', 'Verma', 'neha.verma@example.com', 'E004', 3),
('Sahil', 'Mehta', 'sahil.mehta@example.com', 'E005', 2);

5. Query the Employees Table:

Let’s perform a simple query to verify our data.

SELECT * FROM employees;

Output

In this example, the employee_code column is an alternate key, ensuring that each employee has a unique employee code in addition to the unique primary key (id).

Adding an Alternate Key to an Existing Table

Let’s assume we have a table projects and we want to add an alternate key to it.

1. Create the Projects Table:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_code VARCHAR(10),
    department_id INT
);

2. Add an Alternate Key to the Projects Table:

ALTER TABLE projects
ADD CONSTRAINT unique_project_code UNIQUE (project_code);

3. Insert Data into the Projects Table:

INSERT INTO projects (project_name, project_code, department_id) VALUES
('Project A', 'P001', 1),
('Project B', 'P002', 2),
('Project C', 'P003', 1),
('Project D', 'P004', 3);

4. Query the Projects Table:

Let’s perform a simple query to verify our data.

SELECT * FROM projects;

Output

In this example, the project_code column is an alternate key, ensuring that each project has a unique project code in addition to the unique primary key (project_id).

Important Considerations

• Uniqueness: Alternate keys ensure that the values in the specified columns are unique across all rows in the table.
• Multiple Alternate Keys: A table can have multiple alternate keys, each ensuring the uniqueness of different sets of columns.
• Indexing: Alternate keys are automatically indexed in MySQL, improving the performance of queries that use the alternate key for lookups.
• Data Integrity: Alternate keys help maintain data integrity by ensuring that certain columns have unique values, preventing duplicate entries.

Conclusion

The alternate key is an important feature in MySQL for ensuring that all values in a column or a set of columns are distinct from each other, apart from the primary key. This chapter covered the concept of alternate keys, the syntax for defining them, provided a full example with single and composite alternate keys, and discussed important considerations.