Constraints in SQL Server

Constraints in SQL Server play a big role in keeping data accurate in a database.

Within SQL Server there are several methods of doing this and one way is using constraints

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value when specified none for this column

This is the code to assign a NOT NULL constraint to a Column. Column1 doesn't allow NULLS, Column2 allows NULLS.

CREATE TABLE TestTable1

(

PKID int NOT NULL,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

This is the code to assign UNIQUE constraint to a Column

CREATE TABLE TestTable2

(

PKID int NOT NULL UNIQUE,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

This is the code to assign Primary Key constraint to a column

CREATE TABLE TestTable3

(

PKID int PRIMARY KEY,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

If you assign a primary key to a column, it automatically gains a unique constraint. You can have many unique constraints on a table, but you can only have 1 primary key. Each table should have a primary key and primary keys cant have null values.

This is the code to assign a foreign key constraint to a column

CREATE TABLE TestTable4

(

P_Id int PRIMARY KEY,

PKID2 int NOT NULL,

Column1 int NOT NULL,

CONSTRAINT FK_CON FOREIGN KEY (P_Id)

REFERENCES TestTable3(PKID)

)

A foreign key constraint is used to make sure that data can't be deleted if it matches data in another table.

This is the code to assign a CHECK constraint to a column

CREATE TABLE TestTable5
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL,
CONSTRAINT chk_Column1 CHECK (Column1> 0 AND Column2='Test')

)

This makes sure that the value in Column1 is greater than 0 and Column2 contains the word 'Test'

This is the code to assign DEFAULT constraint to a column

CREATE TABLE TestTable6
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL DEFAULT ('Test'),

)

If a value is not entered in column2, the word 'Test' will be inserted.