Hello friends, We have already explain regarding IIF() in SQL Server,Right Join in SQL Server,left join in SQL, Self Join in SQL Server , RANK(),  DENSE_RANK(), ROW_NUMBER() and  Aggregate functions in sql server. Today I am going to explain regarding Constraints in SQL Server.

Data types are the way to limit the kind of data that can be stored in a table.For that, SQL allows you to define constraints on columns and tables. SQL Server uses constraints to enforce limitations on the data that can be inserted in table. If a user attempts to insert data in a column that would violate a constraint, an error raised.

Check Constraints
A Check Constraint is allow you to specify that the value is in certain condition. For example, if you want positive value in column you can specify constraint in table:

CREATE TABLE Product(
product_id Int,
product_name varchar(150),
Price Numeric CHECK (Price > 0)
);

It will not allow negative value to insert in table. If trying to insert any out of range value it will through exception.
If you want to specify name to constraint, use key word CONSTRAINT while declaring constraint.

CREATE TABLE Product(
Product_id INT,
Product_name VARCHAR(150),
price Numeric CONSTRAINT price_check CHECK (price > 0)
);

You can specify multiple condition in one check using logical expressions.

NOT NULL Constraints
A not null constraints simply specifies that a column must not assume the null value. For example,

CREATE TABLE Product(
Product_id int NOT NULL,
Product_name VARCHAR(150),
price numeric
);

If it was not declared it will default NULL constraint, means you can insert NULL value in that columns. If NOT NULL Constraint declared and trying to insert NULL value in that column it will through exception.

UNIQUE Constraints
Unique constraint ensure that the data contained in a column is unique with respect to all the rows in the table. For example,

CREATE TABLE Product(
Product_id int UNIQUE,
Product_name VARCHAR(150),
price numeric
);

It is also possible to assign name to UNIQUE constraint:

CREATE TABLE Product(
Product_id int CONSTRAINT diff UNIQUE,
Product_name VARCHAR(150),
price numeric
);

It will not allow two same value value for the column in which UNIQUE Constraint declared. While doing so it will through exception. It will allow only unique value in that column.

Primary Key Constraints
A primary key indicates that a column can be used as a unique identifier for rows in the table. A table can have at most one primary key. Primary key is a combination of UNIQUE constraint and NOT NULL constraint. For Example,

CREATE TABLE Product(
Product_id int UNIQUE NOT NULL,
Product_name VARCHAR(150),
price numeric
);

CREATE TABLE Product(
Product_id int PRIMARY KEY,
Product_name VARCHAR(150),
price numeric
);

Above both table definition accept the same data. It will not allow two same value in column. Main difference between Unique and Primary key constraint is that Unique Constraint allow only one NULL value in column but Primary key will not allow NULL value.

Foreign Key Constraints
A foreign key constraint specifies that the values in the column must match the value appearing in some rows of the another table. It will create relation between two tables. For example, you have product table:

CREATE TABLE Product(
Product_id int PRIMARY KEY,
Product_name VARCHAR(150),
price numeric
);

Assume you have table order store order of those products. Now we want to ensure that only existing product will insert in order table. So we define foreign key constraint in the order table that referencing the Product table:

CREATE TABLE order(
order_id int PRIMARY KEY,
Product_id int REFERENCES Product(Product_id),
Quantity int
)

Now it is impossible to create order with product_id entries that do not appear in the Product table.
A foreign key must reference columns that are either a primary key or forma a unique constraint.

Your queries and reviews are most welcome.
Thanks 🙂