SQL SERVER FULL COURSE
PART - 2
ALL YOU NEED TO KNOW TO GET GOING
WITH MS SQL SERVER
C#.NET 1
MS SQL SERVER FULL COURSE
Constraints – What is Data Integrity?
• Process of ensuring that the data contained in the database is accurate, consistent, and
reliable is known as Data Integrity.
• To ensure data integrity, RDBMS provides us with a set of integrity constraints that
ensures that the data entered the database is going to be accurate, consistent, and
reliable.
• Data Integrity is most important because Data Integrity is the reason end-user can trust
the data.
2
MS SQL SERVER FULL COURSE
Constraints – What is a Constraint?
• A Constraint is a property that can be assigned to a column or columns of a table to
maintain data integrity.
• Constraints are used to restrict the insert/update/delete of unwanted data to maintain
data integrity.
• Constraint can be created on single or multiple columns of a table in SQL Server.
• Types of SQL Server Constraints
• Default Constraint
• UNIQUE KEY constraint
• NOT NULL constraint
• CHECK KEY constraint
• PRIMARY KEY constraint
• FOREIGN KEY constraint
3
MS SQL SERVER FULL COURSE
Constraints – Ways to Impose Constraints
• We can impose constrains in two ways on a table
• On Column
• On Table
4
MS SQL SERVER FULL COURSE
Constraints – Composite Constrains
• A constraint that is created based on more than one column is called as a Composite
Constraint.
5
MS SQL SERVER FULL COURSE
Constraints in Action
• Default Constrains
• NOT NULL Constrains
• UNIQUE Constrains
• CHECK Constrains
6
MS SQL SERVER FULL COURSE
Constraints in Action
• PRIMARY KEY Constrain
• It is the combination of UNIQUE and NOT NULL Constrains.
• It will not allow NULL or Duplicate values into a column on which PK is applied.
• Primary Key enforces entity integrity i.e., using PK we can identify a record uniquely in a table.
• Composite PK Constrain
• Primary Key constraint containing more than one columns is called a Composite Primary Key.
• A Composite PK can include maximum 16 columns.
• A Composite PK can be created at the table level, it cannot be created at the column level.
• Note: In a composite primary key, each column can accept duplicate values, but the
combination of all columns should not contain duplicate values.
• Question – What is the difference between PK and UK?
• Share your thoughts in comment box.
7
MS SQL SERVER FULL COURSE
Constraints in Action
• FOREIGN KEY Constrain
• A foreign key in a table points to the primary key or unique key in another table.
• The foreign key constraints are used to enforce referential integrity.
• Creating relationship between the database tables is one of the most important concepts in a
database.
• These relationship provides a mechanism for linking the data stored in multiple tables and
retrieving them in an efficient manner.
• We can specify an FK in a table that references a column in another table to create a link
between two tables.
• That means Foreign Key constraint is used for binding two tables with each other and then
verify the existence of one table data in other tables.
8
MS SQL SERVER FULL COURSE
Constraints in Action
• Rules to create FK Constrain
• Both the tables must have a common column for linking the tables.
• The common column in both the tables need not have the same name but must have the same
data type.
• The common column in parent table or master table is known as the reference key column and
should not contain any duplicate values.
• So, this column must be UNIQUE or PRIMARY KEY column.
• The common column present in the child or detailed table is known as the Foreign KEY
column.
• We need to impose a Foreign KEY constraint on the column which refers to the reference key
column of the master table.
9
MS SQL SERVER FULL COURSE
Constraints in Action
• Rules for FK Columns
• Rule1 – Cannot insert a value into the foreign key column if value is not existing in the
reference key column of the parent (master) table.
• Rule2 – Cannot update the reference key value of a parent table provided that the value has a
corresponding child record in the child table without addressing what to do with the child
records.
• Rule3 – Cannot delete a record from the parent table if records reference key value has child
record in the child table without addressing what to do with the child record.
• Question – What is the difference between Primary Key and Foreign Key Constraint in
SQL Server?
• Share you answer in comment box.
10
MS SQL SERVER FULL COURSE
Constraints in Action – Cascade Referential Integrity
• Cascading Referential Integrity Constrains are the foreign key constraints that tell SQL Server
to perform certain actions whenever a user attempts to delete or update a primary key to
which an existing foreign keys point.
• SET NULL
• Records in the child table will be set to null when the related record in the parent table will be deleted or
updated
• CASCADE
• Records in the child table will be deleted/updated when the related record in the parent table will be
deleted or updated
• SET DEFAULT
• Records in the child table will be set to default when the related record in the parent table will be
deleted or updated.
• It requires a default constraint defined on the child table.
• NO ACTION
• This is the default action. The action will be denied and rolled back.
11
MS SQL SERVER FULL COURSE
Constraints in Action – Cascade Referential Integrity
12
13
THANK YOU