What is the constraints in SQL Server?

Constraints set the rules and regulation on the columns and table to insert data.
SQL constraints keep watch on data accuracy and reliability in database .


Below image will explain the flow of transaction.


Types of constraints in SQL Server

   NOT NULL : Not allow to store the NULL value in columns
   UNIQUE : It make sure that columns should have unique value
   CHECK : Checks specific condition before inserting data into a table.
   DEAULT : At time of data insertion if we not provide the value then it take default value.
   PRIMARY KEY : Create index on table and keep unique and not null value in columns
   FOREIGN KEY : Create relationship between two table

Let's start with first one.

NOT NULL constraint in SQL Server

NOT NULL constraint makes sure that NULL value should not insert into table. We can apply NOT NULL constraint on column by two way.

1) Add NOT NULL constraint on table by Query.
In below example we are creating new table of student and make the StudentName as NOT NULL constraint.

Example

CREATE TABLE tblStudentDetails
(
Id INT ,
StudentName VARCHAR (50) NOT NULL,
City VARCHAR (50)
)

2) Add NOT NULL constraint on table using table design.

add not null constraint on table in sql server

Below we are trying to insert NULL values in StudentName but StudentName columns has NOT NULL constraint.

Example

INSERT INTO tblStudentDetails (Id, StudentName, City)
VALUES (1, NULL, 'Pune')

Output

can not insert null value into column error


Provide the correct data to StudentName and try to insert data once again. It show data inserted successfully.

Example

INSERT INTO tblStudentDetails (Id, StudentName, City)
VALUES (1, 'Ganesh', 'Pune')

Output

can not insert null value into column error solution


Add NOT NULL constraint on existing columns of table.

Example

ALTER TABLE tblStudentDetails
ALTER COLUMN City VARCHAR (50) NOT NULL

Output

add non null constraint on existing column in sql server


Note : After alter existing table columns it will same reflect in table design. Check below design of table.


add non null on existing column in sql server