Wednesday, October 17, 2012

NOT NULL Constraint

Posted by Rahul Kharde at 11:22 AM

NULL is unknown or missing data in table.


When you create a new NOT NULL constraint on a column, SQL Server checks the column’s current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of a NULL value fail.


Example


CREATE TABLE Employee
(
          EID INT PRIMARY KEY IDENTITY NOT NULL,
          ENAME VARCHAR(50) NOT NULL,
          SALARY DECIMAL(10,2) NOT NULL,
          DEPT VARCHAR(50)
)

INSERT INTO Employee(ENAME,SALARY,DEPT)
VALUES('abc',20000,'HR')


The following SQL enforces the "EID" column and the "ENAME, SALARY " column to not accept NULL values: 
 

INSERT INTO Employee(ENAME,DEPT)
VALUES('pqr','HR')

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'SALARY', table 'TaskTrack.dbo.Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.






If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top