Thursday, June 27, 2019

SQL Server - How does Not Null Constraints Work

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

What is Not Null constraints?
NOT NULL: This constraint ensures that the column has a value and the value is not a null value. A space or a numeric zero is not a null value and can defined at Column level only. The basic rule for Not Null constraint is that A Not null constraint restrict the insertion of null values into a column. 

How can we create Not Null constraints?
We can create Not Null constraints at two levels -
Column Level Not Null Constraints - We can define Not Null constraints with CREATE TABLE statement or in time with table definition. 
For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL. 

---- Table Definition with Not Null Constraints
CREATE TABLE TBL_EMPLOYEE
(
EmpId int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
Age int NOT NULL
)

---- Insert values into table
INSERT INTO TBL_EMPLOYEE
(EmpId, FirstName, LastName, Age)
VALUE
(101,’Ryan’,’Arjun’,22)

Thus, we cannot insert the null values in these columns whenever we are going to insert/update the data into this table.

Table Level Not Null Constraints -
If you have inserted the data into your data table and want to add new column with Not Null Constraints then SQL will not allow this operation on that table because existing data does not have any values for this new column. Hence,
  1. You can add new column with NOT NULL having default definition
  2. Or new column with NOT NULL being added is an identity
  3. Or timestamp column

For example,
---- NOT NULL being added is an identity
ALTER TABLE TBL_EMPLOYEE
ADD Id int NOT NULL IDENTITY(1,1)

---- NOT NULL having default definition
ALTER TABLE TBL_EMPLOYEE
ADD CreationDate DateTime NOT NULL Default(GetDate())


Conclusion Once NOT NULL constraint is applied to a column, we cannot pass a null value to that column. It enforces a column to contain a proper value.

To see the demo, please visit our YouTube Channel -

No comments:

Post a Comment