Primary key is a key that uniquely identifies a record in a table. It cannot accepts a null value. We cannot have multiple primary key in a table. It is clustered Index by default. Primary key contain unique value for each row of data. We cannot have two primary key in a table. When we use multiple primary key in a table it is said to be composite key.

For declaring any row as unique by primary key we use Primacy key. Let us take an example to create primary key in a table

 [dbo].[Employee](
          [Id] [int] not null,
          [S_Id] int Primary Key,
          [Name] [varchar](50) CREATE TABLE NULL,
          [Salary] [varchar](50) NULL
) GO

Adding primary key when a table have been already created

alter table Employee  add
primary key (Id)
 
table_1 is the table name where we have to add primary key
To remove primary key
alter table Employee  drop primary key
 

we can also add primary key by going in the design view of table and right click on the field which we have to make primary then add primary key and we can also remove primary key in the same manner .

Foreign Key

Foreign key is use to connect two table .Foreign key is key that is primary key in another table. It accept Multiple Null values. A table can have multiple foreign key. It is also known as referencing key. 

CREATE TABLE [dbo].[E_Salary](
          [Id] [int] Primary Key ,
          [S_Id] [int](50) NULL FOREIGN KEY REFERENCES E_Salary(S_Id),//Declaring  Foreign Key
          [Name] [varchar](50) NULL,
          [Salary] [varchar](50) NULL
)
GO

 

 Adding foreign key when table has been already created

ALTER TABLE E.Salary
ADD FOREIGN KEY (S_Id)
REFERENCES Employee(S_Id)

Difference between primary key and foreign key

Primary key-

  • A table can have one primary key.
  • It cannot accept duplicate values.
  • It is default clustered index
  • It cannot accept null value

Foreign Key-

Default is Non_Clustered Index

A table can have more than one foreign key

It can accept duplicate value.

It accept null value.

It provide link between data in two table.

You can also visit related articles and blogs url

 Sql keys

Foreign Key (Self Reference) Constraint


  Modified On Mar-17-2018 03:06:59 AM

Leave Comment