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). A NOT NULL constraint keeps those NULL values out of a column.
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
What to Check Before You Add a NOT NULL Constraint
Adding the rule to a new table is easy. Adding it to a table that already has data needs a bit more care. If even one row holds NULL in that column, the ALTER COLUMN statement fails. Run a quick SELECT COUNT(*) FROM YourTable WHERE YourColumn IS NULL first, and fill those rows with a sensible value before you change the column.
A few more things I have learned along the way:
- When you use ALTER COLUMN, repeat the full data type with its length, as the example does. Otherwise you may change the type or the size by accident.
- To add a brand new column that does not allow NULL to a table that already has rows, give it a
DEFAULTso the existing rows get a value. - Always write NULL or NOT NULL in your CREATE TABLE. If you leave it out, the result depends on session and database settings, and that is not something you want to guess.
- Check the result with
SELECT name, is_nullable FROM sys.columns WHERE object_id = OBJECT_ID('YourTable').
One more note: unlike a CHECK or UNIQUE constraint, this rule does not get its own name or its own row in sys.objects. It is a property of the column, which is why you change it with ALTER COLUMN and not with ADD CONSTRAINT.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
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.
how to drop not null constraint
Hi , pinal..it was a nice journey watching ur website….
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
ho do i disable null constraint for a time being?
How to drop system generated not null constraints in Sql server
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
its not possible in sql server as far as i know.
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
hi pinal,
all your posts and articles are very helpful. Thanks
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?
What is the difference between the below queries & how it works?
select * from table where col is not null
select * from table where col ”
What is the difference between the below queries & how it works?
select * from table where col is not null
select * from table where col <>”
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
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)
In first case the contraint name is determined by SQL Server whereas in second case you can specify whatever name you want
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