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