SQL Keys: Alternate Key

Syntax

An alternate key in SQL is defined using the following syntax:

CREATE TABLE table_name (
  column1 data_type ,
  column2 data_type ,
  .
  .
  .

  CONSTRAINT constraint_name UNIQUE (column_name)
);

In this syntax, the CONSTRAINT keyword is used to create a unique constraint on a column or a group of columns.

Example

Let's create a table employees with an alternate key on the email column.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE,
  address VARCHAR(255)
);

Output

When the above code is executed, it will create a table employees with an alternate key on the email column.

Explanation

An alternate key is a column or a group of columns that is used to uniquely identify each row in a table, just like a primary key. The only difference is that a table can have multiple alternate keys, while it can have only one primary key.

An alternate key is created using the UNIQUE constraint, which ensures that each value in the column or the group of columns is unique.

Use

The use of an alternate key is to provide an additional way of uniquely identifying the rows in a table. For example, in the employees table, we can identify each employee using either the id column or the email column.

Important Points

• An alternate key ensures that each value in a column or a group of columns is unique.
• A table can have multiple alternate keys, while it can have only one primary key.
• An alternate key can be used to uniquely identify the rows in a table.

Summary

In this tutorial, we learned about the alternate key in SQL, its syntax, an example of creating an alternate key, its output, explanation, use, important points, and a summary. The alternate key is useful in providing an additional way of uniquely identifying the rows in a table.