SQL Keys

SQL Keys – Primary Key, Foreign Key & Unique Key: SQL Keys are used to uniquely identify a specific row in a table and establish relationships between multiple tables. These SQL Keys combine one or more columns from the table to create an identifier that distinguishes it from other rows.

These SQL Keys come in many forms, such as super key, candidate key, primary key, and foreign key; alternate or compound composite keys are available! All these types of SQL keys use constraints to ensure each record is distinct.

Types of SQL Keys

Several types of SQL keys can be used to access records from tables, as well as to form connections between different tables or views. Here is the list:

Now lets us try to understand very basic about those SQL Keys.

Super Key

A super key in SQL is a combination of one or more than one fields that can be used to identify an individual record within a table. This key contains fields that all have unique values. And Primary key, Unique key, and Alternate key are a subset of Super Keys.

Think about the following example to understand this statement better. Check out the table with employee information below:

It is important to note that the EMPNO attribute corresponds to each employee, which makes it unique within this table. This means we can use the Employee Number value alone to identify any data tuple. Therefore, the EMPNO field acts as a Super Key for this table, and other Super Keys may also be present.

For example, a combination of (EMPNO and ENAME) can be used as the Super key for a table since they uniquely identify each tuple. Even though the other attributes included in this key are not necessarily necessary to do so, they still provide accurate identification.

Candidate Key

A Candidate key is a subset of Super keys that don’t contain any unnecessary attributes. Because it only contains the necessary elements to identify tuples uniquely, you can also think of this as the minimal version of a Super key.

Every table must have at least one Candidate Key containing no duplicate values, and all entries must contain some value to ensure accuracy. Tables should include at least one Candidate Key but may possess more than one if necessary.

For instance, we previously mentioned that both EMPNO and ENAME could work as a Candidate Key for the table since they contain unique and non-null values.

Similarly, we cannot use attributes such as EMPNO or ENAME to find specific tuples in the table since they do not have distinct values.

Primary Key

In SQL, a Primary Key is an attribute (or combination of attributes) uniquely identifying each table row. It holds distinct values for every database record and is a powerful tool to ensure data integrity throughout your relational structure.

It’s important to note that there should only be one primary key for any given table, and none of these fields can contain NULL values. Additionally, you may use either single or multiple columns when defining your Primary Key Constraint; if more than one column is specified, this is known as a Composite Primary Key. Finally, it must also be ensured that no two records have the same value in the designated primary key column(s).

What is the Purpose of the Primary Key?

Choosing the correct primary key is a significant factor when creating a database table. Because the purpose of creating a Primary Key is to create an individual identifier for each entry contained within the table, this allows us to access data from that specific record quickly. And also primary key will ensure quick and efficient retrieval when querying results from the database.

Rules For the Primary Key

• In a table, we can have only one primary key.
• A primary key may be single or multiple columns to identify a record uniquely.
• A primary key is UNIQUE, meaning it cannot contain duplicate values. This attribute of the primary key ensures no repeat records in your database.
• A primary key cannot have null values. We can say that primary Keys are NOT NULL by nature.
• A primary key cannot be broken down into smaller parts. There must not be a subset of the primary key that can uniquely identify the attributes of a table.
• A PRIMARY KEY can be used to create a FOREIGN KEY, which references the PRIMARY KEY from another table.

How to Create Primary Key?

By using SQL server, there are two ways to create Primary Key.

• Using T-SQL
• Using SSMS (SQL Server Management Studio)

Create Primary Key Using T-SQL

To create the primary key, we have to configure it with the Create Table script by adding a CONSTRAINT statement at the end of your column declarations. You can check the syntax of declaring the primary key using the script:

CONSTRAINT <primarykey_name> 
PRIMARY KEY [CLUSTERED | NONCLUSTERED] (<column_names>);

Let us see the script to understand better creating primary using T-SQL.

CREATE  TABLE Employee(
EmployeeID int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
EMail nvarchar(50) NULL,
Phone nvarchar(20) NULL,
HireDate date NULL,
ManagerID int NULL,
Salary float NULL,
DepartmentID smallint NULL,
CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID))

When we execute the above script, it will create an Employee table; in that table, the EmployeeID attribute is a primary key.

But sometimes, we want multiple columns as the primary key; to define that, we have to mention the columns in a parenthesis. For Example:

CREATE  TABLE Employee(
EmployeeID int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
EMail nvarchar(50) NULL,
Phone nvarchar(20) NULL,
HireDate date NULL,
ManagerID int NOT NULL,
Salary float NULL,
DepartmentID smallint NULL,
CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeID, ManagerID))

In the above table, each employee can have their unique EmployeeID, but the ManagerID can be duplicated for a few employees. That means Duplicate values may be accepted in a primary key if it contains more than one column. However, each combination of all the columns must be unique.

Create Primary Key Using SSMS

To add a primary key to an existing SQL Server Management Studio table, open the Object Explorer and expand the database. Right-click on the desired table and select Design from the menu that appears. This will bring up a graphical representation of your table. This will open the table designer in the query window.

Click on its row selector to select the column as your primary key. And to create a composite primary key using multiple columns, press and hold down the Ctrl key while selecting all desired rows.

To set EmployeeID as a primary key, right-click over it and then click on the Set Primary Key option in the context menu, as shown below.

Finally, save the table by pressing the CTRL +S or clicking the save icon. After setting the primary key, you can see the key symbol beside the column which you make as the Primary key. And the table will be looks like below:

Add Primary Key In Existing Table

If we have a table that does not have the primary key and want to add a Primary key constraint to that, then we have to use the SQL Alter Statement. Here is an example of adding a primary key to a table,

ALTER TABLE employee_details
ADD CONSTRAINT uq_emp_name
UNIQUE (emp_name);

Delete Primary Key

ALTER TABLE can be used to clear or delete the primary key of a database table. Deleting the primary key is an important process, so please ensure it will not disrupt any data stored in your table before proceeding.

ALTER TABLE Employee 
DROP CONSTRAINT PK_Employee_EmployeeID;   

Alternate key

An alternate key is a candidate key that consists of one or more columns used to identify each row uniquely. These keys are not the primary key, but there may be several candidate keys from which one is chosen as the primary, and all other ones are referred to as Alternate Keys.

Keypoints of Alternate Key

• The alternate key does not allow duplicate values.
• The alternate key does not contain NULL values.
• A table may contain more than one Alternate key.
• The alternate key may have more than one column.
• Suppose the table consists of only one candidate key. It is treated as a Primary key, and then there is no Alternate key in that table.
• All Alternate keys are treated as Candidate keys, but All Candidate keys are not Alternate keys because there is a chance of a Primary key.

To understand the alternate key easily, you can check the below image.

Compound / Composite Key In SQL

A composite key in SQL is a collection of multiple columns that, when placed together, can identify each row uniquely. Rather than relying on just one column to differentiate records from each other, this combination makes identification much easier. Combining different data types or foreign keys might create this primary key type.

Unique Key

In this section, we are going to discuss another SQL Key, which is the Unique Key. A unique key in SQL is a way to ensure that each record in the data table has a unique value. We can think of a unique key as somewhat similar to a primary key since both guarantee uniqueness for every row; however, unlike the primary key, it accepts NULL values but won’t allow multiple NULL values into a column. And we can apply the unique key to multiple columns of the same table.

You can add unique constraints to a table using the T-SQL CREATE or ALTER TABLE command. These constraints should be added after all the column definitions within the CREATE table statement when creating a new table.

The syntax for the define the Unique:

CONSTRAINT <constraint_name> UNIQE(<column_name>)

Using the above syntax, let us create a table using the Unique Key SQL Keys.

CREATE TABLE Employee
(
    EmployeeID INT PRIMARY KEY,
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
    PhoneNo varchar(15),
    CONSTRAINT UNQ_Emp_PhoneNo Unique(PhoneNo)
);

When executing the above script, it will create an Employee table where the EmployeeID is a primary key, and the PhoneNo is the Unique key of the Employee table.

If an existing table does not have the Unique key, then we can add Unique SQL Key in that table by using the Alter Table Statement. You can follow the below script to add a Unique key to an existing table.

ALTER TABLE Employee   
ADD CONSTRAINT UNQ_Emp_Phone Unique(PhoneNo)

If you enter a duplicate value to the Unique key column, then you will get the below error:

Create Unique Key Using SSMS

Earlier, we have seen how to create a table with a unique key using T-SQL. But now we will try to learn the same with SQL Server Management Studio. To add a Unique SQL Key, you can follow the below steps:

Step 1: To add a unique constraint in SQL Server Management Studio (SSMS), log into the database and expand the Tables folder under Object Explorer. Then, please right-click on the table where you want to implement it, select Design from the menu that appears, and open up its designer view.

Now right-click over the column to which you want to assign the unique key, and from the menu, list, and select Indexes/Keys, as shown in the below image.

Step 2: In the “Indexes/Keys” dialog box, Click on Add button, and Select the PhoneNo in the columns field. Then in the Type Field, choose the unique key value in the Columns property, and choose Unique Key type to set a unique key constraint on that field. Enter a name to find out the keys easily.

Click Close and Save the table. In this way, we can create a Unique SQL Key.

Delete Unique Key Constraint

To delete existing unique constraints from a table, use the DROP CONSTRAINT statement in combination with an ALTER TABLE command.

ALTER TABLE 
DROP CONSTRAINT <constraint_name>;

Let us try to remove the unique key we created for the employee table.

ALTER TABLE Employee1   
DROP CONSTRAINT UK_Employee_PhoneNO;

We can also delete the Unique SQL key by using SSMS. To do that, expand the table and then goes to keys and expand that also. On expanding that, you can see the key, right-click over that and select delete.

This will open the “Delete Object” dialog box, as shown below.

To Delete the SQL Key Unique Key, click the OK button.

Foreign Key In SQL

Earlier in this post, we understood one of the SQL keys, the Unique Key, and after that, we will discuss another important SQL key, the Foreign key, and its use.

What is Foreign Key?

A foreign key establishes a connection between two SQL server tables and ensures that referential integrity is maintained. It can be an attribute or multiple attributes within one table that point to another table’s primary key.

Example: The Employee table has a foreign key column, DepartmentID, linked to the Department table’s primary key. This relationship allows us to understand how each employee belongs in their respective department.

Create a Foreign Key

We can assign a foreign key to a column by following two approaches, that’s are:

• Using T-SQL
• Using SQL Server Management Studio

Now we will see how to add a foreign key.

Create a Foreign Key Using T-SQL

In a create table T-SQL script, a foreign key can be set up by adding the CONSTRAINT REFERENCES statement to the end of all column declarations. The syntax will be like below:

CONSTRAINT <foreignkey_name> FOREIGN KEY (<column_name>)
REFERENCES <reference_tablename> (<column_name>)
ON DELETE CASCADE
ON UPDATE CASCADE

Now we will see how to use the above syntax for adding foreign keys in a table. The below T-SQL script generates a new table named Employee and sets up the DepartmentID column with an FK_Employee_Department foreign key that links to the primary key of the Department table.

CREATE TABLE Employee(
EmployeeID int IDENTITY (1,1) NOT NULL,
FirstName nvarchar (50) NOT NULL,
LastName nvarchar (50) NOT NULL,
DepartmentID int NULL, 
CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID), 
CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID)
REFERENCES Departments (DepartmentID));

By following the below syntax & with the help of Alter command, we can add a Foreign key to an existing table.

ALTER TABLE <table_name>
ADD CONSTRAINT <foreignkey_name> FOREIGN KEY (<column_name>)
REFERENCES <reference_tablename> (<column_name>)
[ON DELETE CASCADE]
[ON UPDATE CASCADE]

Example:

ALTER TABLE Employee    
ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID)     
REFERENCES Department (DepartmentID)     
ON DELETE CASCADE    
ON UPDATE CASCADE  

Conclusion:

Finally, we ended, but in this blog post, we have guided you on details on SQL Keys and different types of SQL keys. But still, if you have any concerns about the SQL keys, you can contact us in the comment section of this blog post, and we will try to help you.