DATABASE KEYS in SQL Server

SUPER KEY

Super Key is an Attribute or a Set of Attributes that can uniquely define a row in a table. In the below Query, we have created Employee Table and we will try to see the columns which can be considered a SUPER KEY.

IF OBJECT_ID(N'EMPLOYEES', N'U') IS NOT NULL           
DROP TABLE EMPLOYEES;

CREATE TABLE EMPLOYEES
(Emp_id int,F_Name varchar(20),L_Name varchar(20),
email varchar(30),Phone varchar(30));

insert into employees values
(1,'Khusbu','Saxena','ksaxe@abc.com','111-222-333-4444'),
(2,'Ranjit','Sharma','Ransha@abc.com','112-223-333-4444'),
(3,'Sampath','Shaik','Samsha@abc.com','113-224-333-4444'),
(4,'Heidi','Murata','Heimur@abc.com','114-225-333-4444'),
(5,'Jennifer','lui','Jennlui@abc.com','115-226-333-4444');

select * from employees;

In the below table “Emp_id”, “email” and “Phone” are the attributes that can uniquely define a row in a table.

This means that if we know the value of “EMP_id” or “email” or “phone” we can search the value of the rest of the columns.

All the below Combinations are SUPER KEYS

EMP_ID, EMAIL, PHONE

EMP_ID + EMAIL , EMP_ID + PHONE , EMAIL + PHONE

EMP_ID + EMAIL + PHONE

CANDIDATE KEY or UNIQUE KEY

IF any Proper Subset of a SUPER KEY is a SUPER KEY then that column is not a candidate key.

In the below example, EMP_ID, EMP_ID + EMAIL, and EMP_ID + EMAIL + PHONE all are SUPER Keys, which means all these columns or combinations of columns can uniquely define a row in a table, but a SUBSET of (EMP_ID + EMAIL + PHONE) is (EMP_ID + EMAIL) which is also a SUPER KEY, so (EMP_ID + EMAIL + PHONE) is not a CANDIDATE KEY.

Now let’s check (EMP_ID + EMAIL) , Both (EMP_ID) & (EMAIL) are SUBSET of (EMP_ID + EMAIL)  and they both are SUPER KEYS which means they can individually define a row in a table so (EMP_ID + EMAIL) is also not a CANDIDATE KEY.

Both EMP_ID & EMAIL are super keys and both can individually define a row in a table there is no further subset of them so they both can be considered as a CANDIDATE KEY.

EMP_ID + EMAIL + PHONE

EMP_ID + EMAIL

EMP_ID

EMAIL

So the conclusion is that all the below IDs are the candidate keys as they all are SUPER KEYS and there is no proper subset of these columns that are SUPER KEYS.

EMP_ID

EMAIL

PHONE

PRIMARY KEY

A primary key is a Column or a Composion of columns that uniquely define a row in a table, as we saw above that all the CANDIDATE KEYS can uniquely define a row in a table, A database administrator can choose any one of the candidate keys and that candidate key becomes a PRIMARY KEY, please note that there can only PRIMARY key that can exist in a database.

ALTERNATE KEY

All of the CANDIDATE KEYS that are not selected as a PRIMARY KEY become ALTERNATE KEYS.

FOREIGN KEY

In a Relational database, a foreign key helps in defining a relationship between one table with another Foreign key helps maintain data integrity for a table 

COMPOSITE KEY

Any KEY that is a Composition of more than 1 Attributes are COMPOSITE KEY, All the below columns / Combination of columns are considered as COMPOSITE KEYS.

EMP_ID + EMAIL + PHONE

EMP_ID + EMAIL

EMP_ID + PHONE

EMAIL + PHONE

COMPOUND KEY

If a COMPOSITE KEY has at least one attribute as a FOREIGN KEY then that whole Attribute is a COMPOUND KEY.