Constraints in SQL Server

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 a 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 a 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 one 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.

Leave a Reply

Your email address will not be published. Required fields are marked *