Saturday, October 12, 2013

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

No comments: