SQL-How to Add NOT NULL Constraint to a Table

There are certain columns that we never want to leave blank or with undefined values. In short certain columns cannot be null. Null is anything that is undefined or non-existing. So, in this tutorial you will learn how to add NOT NULL constraint to a table.

NOT Null is strictly a column level constraint. Can you guess why? Yes you guessed it right!! We can check Null-ness of one column at a time. It will not be appropriate that we check the NULL status of a set of columns together.

Any column that is defined with a NOT NULL constraint will inhibit the data insertion unless some data is provided to it in the insert query. You cannot leave the column empty while inserting data. If you have to fill in only selected columns of a table, you cannot do it if you haven’t included the NOT NULL column in the column list.

NOT NULL is not same as a primary key. For example you want that all the customer records must be saved along with a phone number. You don’t want to miss any phone number but you don’t want to uniquely identify customers by their phone number. In such case make phone number a NOT NULL column. While inserting customer records Oracle will not save it if Phone Number column is supplied a blank value.

Syntax

Column-name DATATYPE(size) [CONSTRAINT constraint-name] NOT NULL,

  • Column-Name is the name of the column that cannot be left blank at time of data insertion or update
  • CONSTRAINT Constraint-name- the keyword and the name of the NOT NULL constraint (this is optional)
  • NOT NULL keywords

NOT NULL Constraint Without Constraint Name

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustCity varchar2(30),
CustPhone varchar2(15)NOT NULL,
CustCreditScore number(4),
CONSTRAINT pk_custid PRIMARY KEY (CustID)
);

NOT NULL Constraint With Constraint Name

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustCity varchar2(30),
CustPhone varchar2(15)CONSTRAINT NN_custphone NOT NULL,
CustCreditScore number(4),
CONSTRAINT pk_custid PRIMARY KEY (CustID)
);

Points to Remember

  • NOT NULL is neither zero nor an empty string. The value is just not there.
  • You can make multiple NOT NULL columns in one table. All must be defined individually.