Free Web Development Online Tutorials, Learn to Code
Primary Key Foreign key Relationship, Learn SQL primary key constraint, how to set composite primary key

Primary key constraint in SQL Server

In this tutorial you will learn How to create primary key in SQL server database, difference between sql primary key and unique key.

ms sql primary key constraint

A primary key in SQL Table helps to identify each row/record in a table uniquely.

Here is the characteristic of Primary Key in SQL Table

  • Primary keys must contain unique values
  • primary key column cannot have NULL values
  • Table can have only one primary key (can be composite with multiple fields)

All primary keys are unique keys, but all unique keys are not necessarily primary key.

How to create Primary key in SQL

ColumnName datatype not null identity(1,1) PRIMARY KEY, Note: identity(1,1) seed not Necessary while creating the primary key, but in general we set the identity seed for Primary Key column most of the time.

CREATE TABLE ClassStudentMap (
CSId	int not null identity(1,1)  PRIMARY KEY,
ClassId int NOT NULL,
StudentId bigint NOT NULL             
);

You may check the Foreign Key to understand the more insight about Primary Key.

Composite primary key in sql

Composite primary key also known as Composite key in SQL Table, this type of primary key is created when we need more than one column is used to specify the primary key of that table.

Take a look at following Composite key example; I have made [OrderId] and [RefId] both as Composite Primary Key

Create TABLE [dbo].[tbOrder](
    [OrderId] [bigint] NOT NULL,
    [RefId] [varchar](50) NOT NULL,
    [SellerId] [bigint] NOT NULL,
    CONSTRAINT [PK_tbOrder] PRIMARY KEY CLUSTERED 
    (
	    [OrderId] ASC,
	    [RefId] ASC
    )
) ON [PRIMARY]

We could have also written the same above query differently like example below , simply define the column name within PRIMARY KEY ([col1],[col2])

Create TABLE [dbo].[tbOrder](
	[OrderId] [bigint] NOT NULL,
	[RefId] [varchar](50) NOT NULL,
	[SellerId] [bigint] NOT NULL,
	 PRIMARY KEY  ([OrderId],[RefId])
) 

You should also read SQL Foreign Key concept.

Comment
Name
Email
Website
Subscribe
 
PRIMARY KEY Constraint in MS SQL Database

SQL job Interview Questions Answers

Course in Demand
MS SQL Examples | Join MS SQL Course