Different Keys in RDBMS With Examples

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

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.
CustNo CustFName CustLName CustEmail CustGender
121 Vikas Singh Vikas.singh@gmail.com M
122 Chandra Reddy Chandra.r@gmail.com M
123 Laxman Sharma Laxman.s@yahoo.com M

 

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 –

 

OrderNo OrderDate OrderStatus CustNo
198921 23/12/2018 Dispatched 121
198922 02/11/2018 Delivered 124
198923 11/10/2018 Delivered 121

 

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.

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners