lishman levelup
«previous  next»

SQL Basics

Oracle Not Null Constraint

We start with the NOT NULL constraint which makes a column on a table mandatory.

Simply indicate which columns must contain a value by specifying NOT NULL in the column definiiton.
CREATE TABLE continent (
  cont_id    NUMBER       NOT NULL,
  cont_name  VARCHAR2(20) NOT NULL
This guarantees that a column will never contain a null value.
INSERT INTO continent (cont_id, cont_name) VALUES (2, '');
-- SQL Error: ORA-01400: cannot insert NULL into ("LISH"."CONTINENT"."CONT_NAME")

UPDATE continent
SET cont_id = null;
-- SQL Error: ORA-01407: cannot update ("LISH"."CONTINENT"."CONT_ID") to NULL
Specifying just NULL on a column indiciates that nulls are allowed but technically it is not an integrity constraint and is therefore optional.