What is SQL Primary Key

What is SQL primary key?

A SQL primary key is a column that uniquely identifies each row in a table. Primary key can be created for a table by using the PRIMARY KEY constraint.

Primary key is useful when you deal with multiple tables in database, primary key is easy to link two or multiple table to extract query result. Uniqueness of each rows helps to extract correct information when user does the relation queries.

How many ways to define Primary Key?

Primary Key can be performed in two different ways, Bothe ways explained as syntax below or with details example

If the primary key consists of only one column, you can define or use PRIMARY KEY Keyword after the column name:

Syntax:

CREATE TABLE table_name (
            column_1 data_type PRIMARY KEY

            ...
            );

In case the primary key has two or more columns, you must use the PRIMARY KEY keyword at the end of columns name , with specify those columns which you want to make PRIMARY KEY as a table constraint:

Syntax:

CREATE TABLE table_name ( 

             column_1 data_type, 

             column_2 data type, 

             ... 

             PRIMARY KEY (column_1, column_2)

             );

Table can contain only one primary key. if there is multiple columns then the primary key must be defined as NOT NULL. SQL Server automatically sets the NOT NULL for all the primary key columns if the NOT NULL constraint is not specified for those columns.

Example #1: PRIMARY KEY for one Column

Lets take example to create PRIMARY KEY for one column of table, Try to create PRIMARY KEY with those columns which should be unique value.

CREATE TABLE Orders_Tbl(

            Customer_ID int,  PRIMARY KEY IDENTITY

            Customer_name Varchar(255),
 
            );

In this  example “Customer_ID“,  assigned as a PRIMARY KEY for “Orders_Tbl“.  and IDENTITY  property used to create automatically unique Ids for Customers

Example #2: PRIMARY KEY for 2 Column

Lets take example to create PRIMARY KEY for 2 column of table using Create Table command

CREATE TABLE Orders_Tbl(

             Customer_ID int,

             [Order ID] int,

             PRIMARY KEY(Customer_ID, [Order ID])

               );

In this example, the values in Customer_id or Order_id column can be duplicate, but each combination of values from both columns must be unique.

Example #3: PRIMARY KEY using ALTER Statement

Primary Key always defined at the time of table creation. If an existing table may not have a PRIMARY KEY defined. In this case, you can add a PRIMARY KEY to the table by using the ALTER TABLE statement.

Lets take example “Orders_Tbl” don’t have a primary key, How you can add primary Key in existing table

ALTER TABLE Orders_Tbl

      ADD PRIMARY KEY(Order_id);