Adding a NOT NULL Constraint

The following ALTER TABLE statement adds a NOT NULL constraint to the ename column:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
                  
SQL> ALTER TABLE emp
  2  MODIFY ename CONSTRAINT ename_nn NOT NULL;

Table altered.

SQL>

The next example adds a NOT NULL constraint to the sal column:


SQL> ALTER TABLE emp
  2  MODIFY sal CONSTRAINT sal_nn NOT NULL;

Table altered.

SQL>

The following statement adds a NOT NULL constraint when creating the table:


CREATE TABLE VENDORS
(ID           NUMBER,
 NAME         VARCHAR2(20),
 STATUS       NUMBER(1) CONSTRAINT STATUS_NN NOT NULL,
 CATEGORY     VARCHAR2(5)
);
Home »
Oracle »
Table » 

Constraints:
  1. Adding a Constraint with CHECK
  2. Adding a NOT NULL Constraint
  3. Adding a FOREIGN KEY Constraint
  4. ON DELETE CASCADE
  5. ON DELETE SET NULL
  6. Adding a UNIQUE Constraint
  7. CHECK constraint
  8. Multiple Constraints
  9. Dropping a Constraint
  10. Disabling a Constraint
  11. Enabling a Constraint
  12. Deferred Constraints
  13. Getting Information on Constraints:user_constraints and all_constraints
Related: