What is Primary Key?

In this post, I will explain What is Primary Key?

Primary Key provides uniqueness for a column or set of columns in a table and used to identify a row (a record) in a table. A database table can have only one Primary Key and a column with a Primary Key doesn't allow NULL value (Not nullable). Primary Key generates a Unique Clustered Index.

Example of Primary Key-

Create Primary Key while creating a table
CREATE TABLE tbl_Employee
(
    EmpId INT NOT NULL,
    EmpName VARCHAR(100) NULL,
    CONSTRAINT PK_EmpId PRIMARY KEY (EmpId)
)

Create Primary Key using alter table statement
ALTER TABLE tbl_Employee
ADD CONSTRAINT PK_EmpId PRIMARY KEY (EmpId)

Drop Primary Key using alter table statement
ALTER TABLE tbl_Employee
DROP CONSTRAINT  PK_EmpId
 

Read More

What is Unique Key?

In this post, I will explain What is Unique Key?

Unique Key provides uniqueness for a column or set of columns in a table like a primary key. It is used to prevent duplicate values in a column. A database table can have more than one Unique Key and it allows at least one NULL value in a column( Nullable). Unique Key generates a Unique Non-Clustered Index

Example of Unique Key-

Create a Unique Key while creating a table
CREATE TABLE tbl_Employee
(
    EmpId INT NOT NULL,
    EmpName VARCHAR(100) NULL,
    CONSTRAINT UC_EmpId UNIQUE (EmpId)
)

Create Unique Key using alter table statement
ALTER TABLE tbl_Employee
ADD CONSTRAINT UC_EmpId UNIQUE (EmpId)

Drop Unique Key using alter table statement
ALTER TABLE tbl_Employee
DROP CONSTRAINT  UC_EmpId
 

Read More

What is Foreign Key?

In this post, I will explain What is Foreign Key?

A Foreign Key is a Column (or Set of Columns) that references a column (most often the primary key) of another table. A table containing the Foreign Key is called a child table, and a table containing Primary Key is called a parent table. 

Foreign Key is used to ensure referential integrity of the data in two tables and to prevent actions that would destroy links between tables. A Foreign Key also prevents invalid data from being inserted into a Foreign Key column, because it has to be one of the values contained in a parent table referenced column.

Example of Foreign Key-

Create Foreign Key while creating a table
CREATE TABLE tbl_Student
(
    StudentId INT NOT NULL,
    StudentName VARCHAR(100) NULL,
    CONSTRAINT pk_StudentId PRIMARY KEY (StudentId)
)

CREATE TABLE tbl_Class
(   
    ClassId  INT NOT NULL,
    ClassName VARCHAR(100) NULL,
    StudentId INT NOT NULL,
    CONSTRAINT pk_ClassId PRIMARY KEY (ClassId),
    CONSTRAINT fk_StudentClassId FOREIGN KEY (StudentId)
    REFERENCES tbl_Student(StudentId)
)

Create Foreign Key using alter table statement
ALTER TABLE tbl_Class
ADD CONSTRAINT fk_StudentClassId
FOREIGN KEY (ClassId) REFERENCES tbl_Student(StudentId);

Drop Foreign Key using alter table statement
ALTER TABLE tbl_Class
DROP CONSTRAINT  fk_StudentClassId

Read More