by Dinesh Thakur Category: SQL Queries

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;



About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.



Search Content