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 (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Joins, SQL NULL, SQL Scripts
Previous Post
SQL SERVER – Three Rules to Use UNION
Next Post
SQL SERVER – 2005 – Driver for PHP Community Technology Preview (October 2007)

Related Posts

18 Comments. Leave new

  • 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.

    Reply
  • how to drop not null constraint

    Reply
  • DileepReddy Namburu
    April 15, 2008 6:12 pm

    Hi , pinal..it was a nice journey watching ur website….

    Reply
  • 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

    Reply
  • ho do i disable null constraint for a time being?

    Reply
  • How to drop system generated not null constraints in Sql server

    Reply
  • 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

    Reply
  • its not possible in sql server as far as i know.

    Reply
  • 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

    Reply
  • hi pinal,
    all your posts and articles are very helpful. Thanks

    Reply
  • 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?

    Reply
  • What is the difference between the below queries & how it works?

    select * from table where col is not null

    select * from table where col ”

    Reply
  • What is the difference between the below queries & how it works?

    select * from table where col is not null

    select * from table where col <>”

    Reply
    • First one find out all rows which are not NULLS but it will return if col is empty
      Second one find out all rows which are not empty it will not return if col is NULL as well

      Reply
  • 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)

    Reply
    • In first case the contraint name is determined by SQL Server whereas in second case you can specify whatever name you want

      Reply
      • Why we create constraint_name for not null, and where or how we can see its name in database

  • 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

    Reply

Leave a Reply