NOT NULL Constraint

In this blog, we’ll discuss about NOT NULL constraint.

In MS SQL NULL means UNKNOWN / MISSING / UNDISCOVERED value.

  1. NOT NULL constraint once added with a column will not allow the column to store the NULL value.
  2. NOT NULL constraint can be added with column added to existing table with / without data / during table definition creation.
  3. NOT NULL constraint can be remove from the column at anytime.
  4. Column set to PRIMARY key is always NOT NULL.It never accepts NULL value.Primary key automatically makes the column NOT NULL even it was not declared while creation.
  5. Column set to UNIQUE key allows a single row with NULL value by default. Until we specify the NOT NULL constraint with the UNIQUE constraint.
  6. Column set to FOREIGN key never allows a NULL value by default.It accepts NULL value only if WITH SET NULL cascade property is set with FOREIGN key definition.
  7. Though SQL Server treated NOT NULL as a Domain constraint, but it never allocates any name for its definition. We cannot find either by procedure ‘sp_helpconstraint’ or under the Constraints group inside the table.

Examples

–Example1: NOT NULL constraint with table definition & NOT NULL constraint with DEFAULT constraint
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null DEFAULT ‘New Delhi’
)

–It’ll throw an error as name cannot be NULL
Insert into test values (1,NULL,DEFAULT)

–It’ll successfully insert a record with ‘New Delhi’ as city
Insert into test values (1,’XXX’,DEFAULT)

–Example2: Adding NOT NULL new column in the existing table
Alter table test Add Gender varchar(1) NOT NULL

–Example3: Alter existing column & adding NOT NULL constraint
Alter table test Add Country varchar(50)
Alter table test Alter column Country varchar(50) NOT NULL

–Example4: Removing NOT NULL constraint
Alter table test alter column Gender varchar(1)

To get all the detail information about all the column having NULL or not in all the tables in the current database

select
@@Servername AS ServerName,
DB_NAME() AS DBName,
t1.name as TableName,
c1.name as ColName,
c1.collation_name as SQLCollation,
t.name as ColDataType,
c1.max_length as ColMaxLength,
c1.precision as ColPrecision,
c1.scale as Scale,
(Case c1.is_nullable When 0 Then ‘No’ Else ‘Yes’ End) as Col_IsNULL
from sys.tables t1
inner join sys.columns c1
on t1.object_id=c1.object_id
INNER JOIN sys.types t                            — Get the column datatypes
ON c1.user_type_id = t.user_type_id
order by 3,8

NULL is also keyword in MS SQL Server. To get more details about NULL please click here.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

2 thoughts on “NOT NULL Constraint

  1. Pingback: NULL keyword | DATABASE
  2. Pingback: Integrity Constraint in SQL | DATABASE

Leave a Reply

Please log in using one of these methods to post your comment:

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