Post
Ask Question

Types of Keys in SQL

Today, we will learn about the different types of keys in SQL. Basically, there are the following types of keys in SQL.

  1. Primary Key
  2. Foreign Key
  3. Unique Key
  4. Composite Key
  5. Alternate Key
  6. Surrogate Key

1. Primary Key

This is the basic key of the SQL table. It identifies a unique row of the table. When you define a primary key, it must contain unique values, i.e. you have to take data while inserting it into the table. It can’t be null, too, i.e. you can’t insert null values to the primary key column.

CREATE TABLE Employees (
    EmployeeID   INT           PRIMARY KEY,
    Name         VARCHAR(100)  NOT NULL,
    EmployeeNo   VARCHAR(100)  NOT NULL,
    Email        VARCHAR(200)  NOT NULL,
    Active       BIT           NOT NULL,
    Phone        VARCHAR(20)   NULL,
    CreatedDate  DATETIME      NOT NULL
);

2. Foreign Key

This key is related to the Primary key. A foreign key is basically the primary key in another table. It is building a connection among tables.

CREATE TABLE Employees (
    EmployeeID   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL,
    Email        VARCHAR(200) NOT NULL,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL
);

CREATE TABLE EmployeeFiles (
    EmployeeFileID INT          PRIMARY KEY,
    EmployeeID     INT,
    CreatedDate    DATETIME     NOT NULL,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

3. Unique Key

A unique key in the table ensures that the value in the row is unique overall. It does almost the same as the primary key, but it allows a null value for the column. For example, in the table below, the email will be unique for all records no duplicate email can be inserted in this table.

CREATE TABLE Employees (
    EmployeeID   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL,
    Email        VARCHAR(200) NOT NULL UNIQUE,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL
);

4. Composite Key

When there is a requirement of uniqueness on the basis of 2 columns, then a Composite key is used. It is basically a primary key on multiple columns. For example, in the below query data will be inserted on the basis of the uniqueness of EmployeeNo and Email.

CREATE TABLE Employees (
    EmployeeID   INT IDENTITY(1,1),
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL,
    Email        VARCHAR(200) NOT NULL,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL,
    PRIMARY KEY (EmployeeNo, Email)
);

5. Alternate Key

When multiple keys are added to a table except the Primary key, then all keys (except the Primary Key) are identified as Alternate Keys. In the following query, EmployeeNo and Email are alternate keys.

CREATE TABLE Employees (
    EmployeeID   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL UNIQUE,
    Email        VARCHAR(200) NOT NULL UNIQUE,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL
);

6. Surrogate Key

When the primary key of the table serves as a unique identifier, it is known as a Surrogate key. It may be database generated (as in the below query) or by another application (not supplied by the user). It is more often a database generated.

CREATE TABLE Employees (
    EmployeeID   INT IDENTITY(1,1) PRIMARY KEY,
    Name         VARCHAR(100)      NOT NULL,
    EmployeeNo   VARCHAR(100)      NOT NULL UNIQUE,
    Email        VARCHAR(200)      NOT NULL UNIQUE,
    Active       BIT               NOT NULL,
    Phone        VARCHAR(20)       NULL,
    CreatedDate  DATETIME          NOT NULL
);

I hope you find it simple and helpful.

Thank you!


Similar Articles