Apr 9, 2020

What is Primary Key in SQL

What is Primary Key in SQL

A column or set of the column, whose value exists and is unique for every record in the table is called a Primary Key.

Each table can have only one primary key, in one table you cannot have 3,4 primary keys.

primary key does not have NULL value i.e. blank or vacant record in the column.

Let's understand this by example,

Example of Primary Key in SQL
Example of Primary Key in SQL  
The above table shows the sales, where
  • Purchase_number shows the order number of the item. It is shown in the table from 1 to 14. you can see there is no repetition of purchase_numbers and there is no Null value in the column.
  • Purchasing_dates represents the date at which the order is placed with respective cutomer_code. You can see customer_code 3 and 4 has the same date. that means the customer 3 and 4 bought the item on the same date. hence the date is repeated, as per our primary key definition, repetitions are not allowed so it cannot be the primary key for this table.
  • Customer_code as you can see it also repeated which means the same customer bought the different items on a different date, hence it also cannot be the primary key.
  • Product_code is also repeated because customers can buy the same product on different dates.

So, From the above table, we can see that all the values in the columns are repeated except purchase_number each purchase is unique, Hence this will be the unique entity and this will be our Primary Key.

A primary key is also a Combination of Columns

A primary key can be the single column entities or a combination of more than one column for example,

Combination of Column Primary Key
Combination of Column Primary Key

"Primary key can be the combination of Purchase_number and Purchasing_date because this combination makes an unique pair in the whole table hence this also be taken as Primary Key."
Primary keys are the unique identifier of the table. Primary keys are used to join the two or more tables and due to this feature the retrieval of data becomes more efficient.

Relational Schema of Primary Key

Relational Schema for a primary key can be written in a particular order, at the top we have to write the name of the table.

The first name in the rectangular box is with the underlined column name is represents the primary key, which is purchas_number in our case.

Relational Schema Primary key
Relational Schema Primary key
"Primary key is always underlined, and remember not all tables have primary key"

 When we combine the relational Schema to the different schemas we get the database schema. and it's very important for the representation of relationships of different schemas of the database.

thank you for reading if you like this article then do post a comment, and share your thought.


Popular Blog Posts