Add NOT NULL Constraint in SQL Server 2005

Today I will explain about the different ways to add a NOT NULL constraint on a column.

Advantages of NOT NULL constraint:
1) You can prevent users to enter null values into this column.
2) The pre-requisite to enforce Primary Key on a Column.

Now Lets get into the details:
There are two ways which i can think of
1) While creating the table using CREATE TABLE statement.
2) Modifiying the table definition using ALTER TABLE statement.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO INT NOT NULL, DNAME VARCHAR(30))

When you are creating a table as part the Column Definition you can specify the Constraint. This is very easy. But most of the cases we get into a position where we have a table in PROD or some where and want to define a Not Null constraint on the column. So the next section is about defining the constraint on existing table.

ALTER TABLE:

Created a table called Dept to store Department information.

CREATE TABLE DEPT(DEPTNO INT, DNAME VARCHAR(30));

ALTER TABLE DEPT ALTER COLUMN DEPTNO INT NOT NULL;

That's it. Now if you use any of following queries you can see the column is now not nullable.

SELECT * FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'DEPT'

or

sp_columns 'dept'

One question here, When you are adding NOT NULL constraint with ALTER TABLE you need to specify the column Data Type. Do you know why??? If you put different datatype then it will not accept!!!!

If you know the answer please post your comment.