MySQL Alternate Key

In MySQL, an alternate key is a column or set of columns that can uniquely identify a record, similar to a primary key, but isn't chosen as the primary key. Alternate keys ensure data uniqueness and provide additional ways to access records. They play a vital role in maintaining data integrity and optimizing database queries. Understanding and implementing alternate keys effectively can enhance database reliability and efficiency, making them a crucial component in relational database design.

Key Concepts

1. Primary Key: A column or a combination of columns that uniquely identifies a row in a table.
   
2. Candidate Key: A column or a set of columns that can uniquely identify a row in a table. Every table can have multiple candidate keys.
   
3. Alternate Key: A candidate key that was not selected as the primary key.

What is an Alternate Key?

An Alternate key is one of the candidate keys that is not chosen as the Primary key of a relation. It refers to an attribute or a set of attributes that can enable the specification of a specific tuple in relation same to the primary key. But, while defining a relation, only one candidate key can be specified as the primary key, and the others are termed as the alternate key.

To define an alternate key, you need to use the 'UNIQUE' constraint, which ensures that all values in a column or a set of columns are distinct.

Syntax:

Here is the basic syntax for defining an alternate key:

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

...

PRIMARY KEY (column1),

UNIQUE (column2)

);

For an existing table, you can add a unique constraint using:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

Examples of MySQL Alternate Key

Creating a Table with an Alternate Key

Let's create a table named 'students' with an alternate key column.

CREATE TABLE students (    
    student_id INT AUTO_INCREMENT,    
    email VARCHAR(100),    
    phone VARCHAR(15),    
    PRIMARY KEY (student_id),    
    UNIQUE (email)
);

In this table:

• 'student_id' is the primary key.
• 'email' is an alternate key because it is unique and can identify a record independently.

Inserting Data into the Table

Now, let's insert some data into the 'students' table and observe how the alternate key column behaves.

INSERT INTO students (email, phone) VALUES ('[email protected]', '1234567890');
INSERT INTO students (email, phone) VALUES ('[email protected]', '0987654321');
INSERT INTO students (email, phone) VALUES ('[email protected]', '1122334455');

Viewing the Table Data

To verify the inserted data and see the alternate key values, execute:

SELECT * FROM students;

Output:

This query will retrieve all records from the students table. The expected output will look like this:

Adding a New Unique Constraint to an Existing Table

You can add a new unique constraint to an existing table to define an alternate key. Let's add a unique constraint to the 'phone' column.

ALTER TABLE students ADD CONSTRAINT unique_phone UNIQUE (phone);

Inserting Additional Data

Now, let's insert a new row into the students table to see the effect of the unique constraint on the 'phone' column:

INSERT INTO students (email, phone) VALUES ('[email protected]', '2233445566');

Viewing the Table Data Again

SELECT * FROM students;

Output:

By adding the unique constraint to the 'phone' column, we ensure that each phone number is unique in the table, effectively making it an alternate key.

Attempting to Insert Duplicate Values

Let's try inserting a duplicate value in the 'email' column to see how the unique constraint enforces uniqueness:

INSERT INTO students (email, phone) VALUES ('[email protected]', '3344556677');

Output:

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'students.email'

The insertion fails because the 'email' column must have unique values, as enforced by the unique constraint.

Conclusion

The concept of an alternate key in MySQL is very useful in maintaining the uniqueness of data in a table. The primary key is the first unique key for a table, but alternate keys also have unique constraints for the same table. By using alternate keys correctly, you ensure that your database remains reliable and consistent, providing additional qualifiers for identifying records uniquely.