The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL>

Then at some point in the future, you add a NOT NULL constraint like this: 

SQL> alter table andrew modify (col1 not null)
  2  /

Table altered.

SQL>

Doing it this way, the constraint is obvious when you describe the table:

SQL> desc andrew
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                       NOT NULL VARCHAR2(1)

SQL>

… and, if you try to add a null value, the error message is self-explanatory:

SQL> insert into andrew values (null)
  2  /
insert into andrew values (null)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORACLE"."ANDREW"."COL1")

SQL>

The second example, which I saw recently, shows how NOT to do it. You start, as before, by creating a table:

SQL> create table fred (col1 varchar2(1))
  2  /

Table created.

SQL>

… then you add a CHECK constraint as follows:

SQL> alter table fred
  2  add constraint con1
  3  check (col1 is not null)
  4  /

Table altered.

SQL>

You cannot see this constraint when you describe the table:

SQL> desc fred
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(1)

SQL>

… and when you try to add a null value, the error message is not very helpful:

SQL> insert into fred values (null)
  2  /
insert into fred values (null)
*
ERROR at line 1:
ORA-02290: check constraint (ORACLE.CON1) violated

SQL>

How to Allow Updates and Inserts on Individual Columns

This was tested on Oracle 11.2. First I created a couple of users:

 

SQL> create user andrew identified by reid

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant create session, create table to andrew

  2  /

 

Grant succeeded.

 

SQL> grant create session to fred

  2  identified by bloggs

  3  /

 

Grant succeeded.

 

SQL>

 

User Andrew created a table with two columns. Note that col1 could be null but col2 could not. You will see why later:

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1

  2  (col1 number,

  3   col2 number not null)

  4  /

 

Table created.

 

SQL> insert into tab1 values (1,2)

  2  /

 

1 row created.

 

SQL>

 

Andrew then allowed Fred to update col1:

 

SQL> grant select, update (col1) on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL>

 

Fred then logged in and updated col1 but, when he tried to update col2, Oracle returned an

ORA-01031:

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1 set col1 = 3

  2  /

 

1 row updated.

 

SQL> update andrew.tab1 set col2 = 4

  2  /

update andrew.tab1 set col2 = 4

              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL> select * from andrew.tab1

  2  /

 

      COL1       COL2

---------- ----------

         3          2

 

SQL>

 

Andrew then allowed Fred to insert values into col1:

 

SQL> conn andrew/reid

Connected.

SQL> grant insert (col1) on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL>

 

Fred tried to insert a value into col1 but Oracle returned an ORA-01400 because col2 could not be null. If you grant insert access to selected columns in this way, you must grant it to all the not null columns in the table:

 

SQL> conn fred/bloggs

Connected.

SQL> insert into andrew.tab1 (col1) values (5)

  2  /

insert into andrew.tab1 (col1) values (5)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("ANDREW"."TAB1"."COL2")

 

SQL> select * from andrew.tab1

  2  /

 

      COL1       COL2

---------- ----------

         3          2

 

SQL>

 

Andrew then granted insert access to col2 instead. Note how the first revoke failed as you cannot revoke a privilege from a single column:

 

SQL> conn andrew/reid

Connected.

SQL> revoke insert (col1) on tab1 from fred

  2  /

revoke insert (col1) on tab1 from fred

              *

ERROR at line 1:

ORA-01750: UPDATE/REFERENCES may only be REVOKEd from

the whole table, not by column

 

SQL> revoke insert on tab1 from fred

  2  /

 

Revoke succeeded.

 

SQL> grant insert (col2) on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL>

 

As col2 was the only not null column in the table, Fred was then able to insert a row successfully:

 

SQL> conn fred/bloggs

Connected.

SQL> insert into andrew.tab1 (col2) values (6)

  2  /

 

1 row created.

 

SQL> select * from andrew.tab1

  2  /

 

      COL1       COL2

---------- ----------

         3          2

                    6

 

SQL>

Constraints (Part 1) - ORA-01400 and ORA-02290

These examples were tested on an Oracle 9 database.

Constraints are used to validate table data. You can add a constraint when you create a table:

SQL> CREATE TABLE MY_TABLE

  2  (MY_COLUMN NUMBER NOT NULL)

  3  /

Table created.

SQL>

Or you can add one to an existing table:

SQL> ALTER TABLE MY_TABLE

  2  ADD CONSTRAINT MY_CONSTRAINT

  3  CHECK (MY_COLUMN < 5)

  4  /

Table altered.

SQL>

Constraints can check INSERT statements:

 SQL> INSERT INTO MY_TABLE VALUES(1)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(5)

  2  /

INSERT INTO MY_TABLE VALUES(5)

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL> INSERT INTO MY_TABLE VALUES(2)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(NULL)

  2  /

INSERT INTO MY_TABLE VALUES(NULL)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("ORACLE"."MY_TABLE"."MY_COLUMN")

SQL>

And they can also check updates:

SQL> UPDATE MY_TABLE

  2  SET MY_COLUMN = 6

  3  WHERE MY_COLUMN = 2

  4  /

UPDATE MY_TABLE

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL>

When you have finished, only the valid data remains in the table:

SQL> SELECT * FROM MY_TABLE

  2  /

 MY_COLUMN

----------

         1

         2

SQL>

Go to part 2