NOT NULL

            It does not allow null values in the specified column.

Apply not null constraint while the time of new table creation:-

Syntax:

Create table <Table name>

(

<Column 1> <Data type> NOT NULL,

<Column 2> <Data type>,

.

.

.

<Column n> <Data type>

)

Example:

CREATE TABLE ConstraintTBL_1

(

ID INT NOT NULL,

Name VARCHAR(10)

)

Syntax to verify the constraint:

SP_HEPL <Table name>

Example:

SP_HELP ConstraintTBL_1

 

• In the above screen ID column is having Nullable value is ‘NO’, means we cannot insert null values into ID column.
• If we try to insert a null value it will throw a message.

 INSERT INTO ConstraintTBL_1 VALUES (NULL,'A')

Message:-

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'ID', table 'kiran.dbo.ConstraintTBL_1'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Apply not null constraint on existing table column:-

            The particular column (which we are going to apply constraint) should not have null values then only we can apply not null constraint.

Syntax:

ALTER TABLE <Table name> ALTER COLUMN <Column name> <Data type> NOT NULL

Example:

ALTER TABLE ConstraintTBL_1 ALTER COLUMN Name varchar(10) NOT NULL

Result:

SP_HELP ConstraintTBL_1

  Remove\Drop Not null constraint:

Syntax:

ALTER TABLE <Table name> ALTER COLUMN <Column name> <Data type>

Example:

ALTER TABLE ConstraintTBL_1 ALTER COLUMN Name varchar(10)

Result:

SP_HELP ConstraintTBL_1