Now, we will see different keys in rdbms with examples. They are  – primary key, Foreign key and Unique keys.

1. Primary Key

• A Primary Key is a column or set of columns in a table used to uniquely identify each row in the table.
• Primary key that contains more than one column is also called as composite primary key.
• One table can combine up to 16 columns in a composite primary key.
• A table can have only one primary key.
• Primary key does not allow null values. Primary key does not allow duplicate values.

In the above database table, CustNo is the primary key which uniquely identifies each row of the table. If we want to find the details of Chandra in the entire customer database, it would be difficult to find if we don’t have something unique which signifies the records of Chandra. There can be many customers whose name is Chandra if the customer database is significantly large. If you know the primary key value of Chandra then you can find that specific record in the table.

At some point if the decision is made to delete some customer (Just for the sake of explanation 🙂 ) then we can delete the entire record by taking CustNo into consideration.

SQL Server: Query for creating a primary key

CREATE TABLE Customer
(
CustNo int NOT NULL PRIMARY KEY,
CustFName varchar (255) NOT NULL,
CustLName varchar (255) NOT NULL,
CustEmail varchar (255) NOT NULL,
CustGender varchar (10)
)

2. Foreign Key

• This key represents relationships between tables.
• The values in the foreign key column are derived from the primary key or unique key of some other table.
• The foreign key column rejects the insert or update of a value if a corresponding value does not currently exist in the master key table.
• The foreign key column should have the same data type and size of the primary key from which it is being derived.

Example:-

Below is the Orders table –

The table has a column i.e. CustNo which is the foreign key in the Orders table and that is linked to the Customer table. If we want to find all the orders which Customer 121 place, then we just need to find the entries which has CustNo=121 in the Orders table along with the details from Customer table.

CREATE TABLE Orders (
OrderNo int NOT NULL PRIMARY KEY,
OrderDate DATE NOT NULL,
OrderStatus varchar(100),
CustNo int FOREIGN KEY REFERENCES Customer(CustNo)
);

3. Unique Key

• All values in a column are unique in case of UNIQUE constraint.
• UNIQUE and PRIMARY KEY ensures uniqueness for a column and a set of columns.
• The only difference between a PRIMARY KEY and a UNIQUE KEY is that a table can have only one primary key and many UNIQUE constraints.
• PRIMARY KEY automatically has a UNIQUE constraint.
• This key does not allow duplicate values.
• A table can have more than one unique key which is not possible by primary key.

SQL Server: Query for creating a UNIQUE Key

CREATE TABLE Customer
(
CustNo int NOT NULL UNIQUE,
CustFName varchar (255) NOT NULL,
CustLName varchar (255),
CustEmail varchar (255),
CustGender varchar (10)
)

That’s end of tutorial on different keys in rdbms with examples.