Data Integrity and Key Constraints in Microsoft SQL Server

Data Integrity and Key Constraints in Microsoft SQL Server

Data Integrity and Key Constraints in Microsoft SQL Server
Image Credits: Microsoft SQL Server 2012

Overview

This post I will explain Data Integrity and Key Constraints in the Microsoft SQL Server. So let’s start.

Data Integrity and Key Constraints in Microsoft SQL Server

What is Data Integrity?

Data integrity means maintaining accurate, reliable, and consistent data during any operations, such as transforming, storing, and retrieving data. Data integrity falls into 4 categories:

1. Entity integrity

2. Domain integrity

3. Referential integrity

4. User-defined integrity

1. Entity integrity (Data Integrity and Key Constraints in Microsoft SQL Server)

A table must have a primary key and columns defined to be the primary key must be unique and not null. This rule ensures that duplicate rows are not inserted in a table.

2. Domain integrity (Data Integrity and Key Constraints in Microsoft SQL Server)

Domain integrity ensures that only a valid range of values is allowed to be stored in a column. Domain integrity can be put into effect by restricting the type of data, the range of values, and the format of data that are allowed to be entered in a column. It also determines whether or not a column should have a NULL value. We can enforce domain integrity while declaring a table, or through stored procedures and triggers, by using key definitions, check constraints, defaults, and nullability.

3. Referential integrity (Data Integrity and Key Constraints in Microsoft SQL Server)

Referential integrity ensures that all the data values in the foreign key column should match the data values in the primary key column. It ensures that the data in the database remains uniformly consistent, accurate, and usable even after making changes in it.

4. User-defined integrity (Data Integrity and Key Constraints in Microsoft SQL Server)

User-defined integrity refers to a customized set of business rules defined by a user, which does not belong to any standard category of integrities, such as Domain or referential. All the integrity types support user-defined integrity.

What is Key Constraint?

SQL Server key constraints are used to specify the rules and methods for the data in a particular table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. the action is aborted, If there is found any violation in between data action and constraint. Constraints can be column level or table level. Column level constraints apply to a column, and table-level constraints apply to the whole table.  The following constraints are commonly used in SQL:

  1. Primary Key Constraint
  2. Unique Key Constraint
  3. Foreign Key Constraint
  4. Default Constraint
  5. Check Constraint
  6. Rule
  7. Default
  8. Not Null
  9. Index

1. Primary Key Constraint

Primary Key enforces the uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls or it is a combination of a NOT NULL and UNIQUE. The primary key uniquely identifies each records or a row withing the table.

The following syntax creates a PRIMARY KEY on the “ID” column on “Persons” table

a.       Column Level

Syntax:

Query - 1

b.      Table Level

Syntax:

Query - 2

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

Query - 3

Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

To create a PRIMARY KEY constraint on the “ID” column when the table is already created, use the following SQL:

Query - 4

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

Query - 5

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To drop a PRIMARY KEY constraint, use the following SQL:

Query - 6

2. Unique Key Constraint

Unique Key ensures that the uniqueness of the column where it is defined. A non-clustered index is automatically created on the column where a unique Key is already defined. Unique Key allows only one NULL Value or it ensures that all values in a column are different.

The following SQL creates a UNIQUE constraint on the “ID” column when the “Persons” table is created:

Query - 7

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

Query - 8

To create a UNIQUE constraint on the “ID” column when the table is already created, use the following SQL:

Query - 9

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

Query - 10

To drop a UNIQUE constraint, use the following SQL:

Query - 11

3. Foreign Key Constraint

When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint or its uniquely identifies a row/record in another table.

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. Look at the following two tables:

“Persons” table:

PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20

“Orders” table:

OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1

Notice that the “PersonID” column in the “Orders” table points to the “PersonID” column in the “Persons” table. The “PersonID” column in the “Persons” table is the PRIMARY KEY in the “Persons” table. The “PersonID” column in the “Orders” table is a FOREIGN KEY in the “Orders” table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

The following SQL query will create a FOREIGN KEY on the column named “PersonID” when the “Orders” table is created:

a.       Column Level

Syntax:

Query - 11

b.      Table Level

Syntax:

Query - 12

Following SQL syntax will help to define the foreign key constraint on multiple columns and also will allow to give a particular name for it –

Query - 13

Use the following SQL: If you want to create a FOREIGN KEY constraint for the column named “PersonID” when the “Orders” table is already created:

Query - 14

Use the following SQL syntax if you wish to give a name for a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns

Query - 15

Use the following SQL, if you want to drop an existing FOREIGN KEY constraint:

Query - 16

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }: Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

NO ACTION: The SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.

CASCADE: Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

Syntax:

Query - 17

SET NULL: All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is deleted. For this constraint to execute, the foreign key columns must be nullable.

Syntax:

Query - 18

SET DEFAULT: All the values that comprise the foreign key are set to their default values when the corresponding row in the parent table is deleted. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable and there is no explicit default value set, NULL becomes the implicit default value of the column.

To know the status of Foreign Key:

Query - 19

Disable Foreign Key Constraint:

Query - 20

Enable Foreign Key Constraint:

Query - 21

To see List table constraints:

Query - 22

4. Default Constraint

Default constraint, when created on some column, will have the default data which is given in the constraint when no records or data is inserted in that column.

The following SQL sets a DEFAULT value for the “City” column when the “Persons” table is created:

a.       Column Level

Syntax:

Query - 23

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

Query - 24

Use the following SQL query if the table is already created but you need to apply a DEFAULT constraint on the column named “City”:

Query - 24

To drop a DEFAULT constraint, use the following SQL:

Query - 25

5. Check Constraint

Check constraint is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns or it is ensured that all values in a column satisfy a specific condition.

The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that you cannot have any person below 18 years:

Query - 25

Use the following SQL syntax, if you want to define a check constraint on multiple columns and want to give a name for CHECK constraint:

Query - 26

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

Query - 27

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

Query - 28

To drop a CHECK constraint, use the following SQL:

Query - 27

If you are inserting a value without matching check then you couldn’t insert value and also throws a message like.

Syntax:

Query - 28

Change the CHECK constraint values of column in existing table:

  1. Temporary Hide/Off the CHECK constraint of a column in existing table:

For Enabling Check Constraints:

Syntax:

Query - 29

For Disabling Check Constraints:

Syntax:

Query - 29

RESTRICT AND CASCADE OPTION: We can use one of the CASCADE or RESTRICT keywords to specify how the MOVE TABLE statement treats dependencies among database objects. If you include neither of these keywords, the default mode is RESTRICT.

a) ON CASCADE Mode: RESTRICT specifies that the table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key, etc) exist. Therefore, if there are dependencies then an error is generated and the object is not dropped.

b) ON RESTRICT Mode: CASCADE specifies that even if their dependencies go ahead with the drop. That means drop the dependencies first and then the main object. So if the table has SPs(stored procedures) and keys (primary and secondary keys) they are dropped first and then the table is finally dropped.

RULES AND DEFAULTS: COUSINS OF CONSTRAINTS

Rules and defaults have been around much longer than CHECK and DEFAULT constraints have been. They are something of an old SQL server standby and are definitely not without their advantage.

The primary thing that sets rules and defaults apart from constraints is in their very nature; constraints are features of the table. Rules and Defaults are actual Objects. Whereas a constraint is defined in the table definition, rules and defaults are defined independently and are then “bound” to the table after the fact.

Rules

A rule is incredibly similar to a CHECK constraint. The only difference beyond that rules are limited to working with just one column at a time. We can bind the same rule separately to multiple columns in a table, but the rule will work independently with each column, and won’t be aware of the other columns at all.

Syntax:

Query - 30

To see the syntax of the existing Rule:

Syntax:

Query - 31

Result:

Query - 32

To bind existing Rule into a column:

Syntax:

Query - 32

To remove existing Rule from a column:

Syntax:

Query - 32

To drop existing Rule:

Syntax:

Query - 33

Defaults

Defaults are even more similar to their cousin – a default constraint – than a rule is to a CHECK constraint. Indeed, they work identically, with the only real differences being the way that they are attached to a table and the defaults(the object, not the constraint) support for a user-defined data type.

Syntax:

Query - 34

To see the syntax of the existing Default:

Syntax:

Query - 35

Result:

Query - 35

To bind existing Default into a column:

Syntax:

Query - 36

To remove existing Default from a column:

Syntax:

Query - 37

To drop existing Default:

Syntax:

Query - 38

6. Not NULL

It is a constraint that ensures that a column cannot have a NULL value.

The following SQL ensures that the “ID”, “LastName”, and “FirstName” columns will NOT accept NULL values when the “Persons” table is created:

Query - 39

To create a NOT NULL constraint on the “Age” column when the “Persons” table is already created, use the following SQL:

Query - 40

7. Index

It is used to create and retrieve data from the database very quickly.

Creates an index on a table. Duplicate values are allowed:

Query - 41

Creates a unique index on a table. Duplicate values are not allowed:

Query - 42

Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

The SQL statement below creates an index named “idx_lastname” on the “LastName” column in the “Persons” table:

Query - 43

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

Query - 43

The DROP INDEX statement is used to delete an index in a table.

Query - 44

Creating a Table With All Constraints:

Query - 44

Constraints Maintenance:

Finding Constraints Related Information:

Query - 45

OR

Query - 45

We will discuss upcoming topics in the next part. So please take care of yourself. if you want to download this tutorial in PDF then please click here to download PDF.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.

Leave a Reply