SQL NOT NULL

By default all columns in a table can contain null values. If you want to ensure that a column must always have a value, i.e. it should not be left blank, then define a NOT NULL constraint on it.

The SQL NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Syntax: (Column_name [data type] [NOT NULL],

OR

(Column_name data type [Constraint <name>] NOT NULL,

Method 1

Query: The following SQL enforces the “P_Id” column and the “LastName” column to not accept NULL values:

SQL> CREATE TABLE Persons (

P_Id number (6) NOT NULL,

LastName varchar2 (15) NOT NULL,

FirstName varchar2 (15),

Address varchar2 (25),

City varchar2 (15)

)

Table created.

Note : In Persons table some persons might have P_Id and some employees might not have any person Id. If you put NOT NULL constraint on P_Id column then you will not be able insert rows for those persons whose P_Id is null.

Method 2

Query: In the following table enforce the Name column not to accept null values.

SQL) CREATE TABLE AA

(NAME VARCHAR2(20) CONSTRAINT NN_AA NOT NULL);

Table Created

SQL) DESC AA;