Oracle Check Constraint

A check constraint defines a condition on one or more columns that each row must satisfy.

For example, suppose we need to ensure that continent names are entered with the first letter of each word in upper case and the rest of the word in lowercase. The INITCAP function in Oracle converts a string to exactly that format so we will use it in our condition.

CREATE TABLE continent (
  cont_id    NUMBER        NOT NULL PRIMARY KEY,
  cont_name  VARCHAR2(20)  NOT NULL CHECK(cont_name = initcap(cont_name))
);

Let's see what happens when we insert some rows.

INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North america');
-- SQL Error: ORA-02290: check constraint (LISH.SYS_C0014705) violated

INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North AMerica');
-- SQL Error: ORA-02290: check constraint (LISH.SYS_C0014705) violated

INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North America');

If we wish to reference two or more columns in a check constraint we must use a different syntax.

CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL  REFERENCES continent,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL CHECK (ctry_area > 0),
  ctry_pop        NUMBER        NOT NULL CHECK (ctry_pop > 0),
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
  CHECK (ctry_pop > ctry_area)
);

Here we validate that the CTRY_AREA and CTRY_POP values are greater than zero and that CTRY_POP is greater than CTRY_AREA. If any of these conditions fail, an error will be returned and the row will not be inserted or updated.

syntax