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)
pinaldave
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?
[...] (Read More Here) [...]
How to drop system generated not null constraints in Sql server