SQL SERVER – Explanation and Understanding NOT NULL Constraint

NOT NULL is integrity CONSTRAINT. It does not allow creating of the row where column contains NULL value. Most discussed question about NULL is what is NULL? I will not go in depth analysis it. Simply put NULL is unknown or missing data. When NULL is present in database columns, it can affect the integrity of the database. I really do not prefer NULL in database unless they are absolutely necessary. (Please make sure it is just my preference, and I use NULL it is absolutely needed).

To prevent nulls to be inserted in the database, table should have NOT NULL constraint. Two ways NOT NULL constraint can be implemented on database.

1) Implement Constraint when Table is created
2) Implement Constraint after Table is created

Following example demonstrates both the way to create NOT NULL constraints.
USE AdventureWorks
GO
-- NOT NULL Constraint when Table is created
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT NOT NULL)
GO
-- NOT NULL Constraint after Table is created
ALTER TABLE ConstraintTable
ALTER COLUMN ID INT NOT NULL
GO
--Clean Up
DROP TABLE ConstraintTable
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – Explanation and Understanding NOT NULL Constraint

  1. The only problem with

    ALTER TABLE ConstraintTable
    ALTER COLUMN ID INT NOT NULL

    is that if there is an index / constraint on this column then the statement fails with

    Msg 5074, Level 16, State 1, Line 1
    The index ‘BLAH’ is dependent on column ‘BLAH’.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN BLAH failed because one or more objects access this column.

    Like

  2. Nikhil,

    If you have created a table like this

    create table Emp (Ename varchar(10) not null)

    You can alter the Table ..

    alter table Emp alter column Ename varchar(10) null

    Like

  3. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 4 Journey to SQL Authority with Pinal Dave

  4. Hello Pinal,

    Well, I confess, I rarely ask experts for assitance, prefering to muddle through and work out a problem. However, I cannot figure out if I am walking repeatedly into a brick wall on this problem.

    Is it possible to add a NAMED not null constraint to a column (presuming only non-null entries in the column at the time of constraint creation)?

    I’ve seen the syntax for adding a non-named constraint and a named constraint at time of table creation or column addition, but I cannot find even a reference for creation of a NAMED not-null constraint after column creation.

    Thank you very much for your time,
    Frank

    Like

  5. Hello Pinal,

    Is there any problem If I change the Not NULL Constraint to NULL Constraint in a Existance Table.

    I have a Table which has a large Amount of data which has a Specific Column and whose Constraint is Not Null But for some reason I have to change that constraint to NULL.

    So, Is there will be any Problem If I change the Not Null Constraint to Null Constraint in a Existance Column of a Table

    With Regards,
    Pinku Raj

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31 Journey to SQLAuthority

  7. when i write
    create table accounts(accno int identity(1001,1) primary key,accnm varchar(50) not null,acctype char(8) default ‘Saving’, city varchar(50) default ‘amravati’,balance float check(balance>=500),atmno int unique),\
    here accnnm take null value n creates the table.
    Is there any other way to define not null value?

    Like

  8. What is the difference between the below queries & how it works?

    select * from table where col is not null

    select * from table where col ”

    Like

  9. What is the difference between the below queries & how it works?

    select * from table where col is not null

    select * from table where col <>”

    Like

  10. What is the difference between the below commands of SQL SERVER 2008 and how it works?

    create table table_name(column_name datatype not null)

    create table table_name(column_name datatype constraint constraint_name not null)

    Like

  11. Dear Pinal,

    This is suryam raju from hyderabad. I am new for database But i am working in one company on database
    As u discussed in your coding standards can i create not null constraint on column at table level

    Like

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s