PL SQL Oracle Check Constraints

What is a check constraint?

A check Oracle constraint allows you to specify a condition on each row in a table.

Note:

A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement is:

CREATE TABLE table_name (column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] );

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For example:

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_id
	CHECK (supplier_id BETWEEN 100 and 9999)
);

In this first example, we've created a check constraint on the suppliers table called check_supplier_id. This constraint ensures that the supplier_id field contains values between 100 and 9999.

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_name
	CHECK (supplier_name = upper(supplier_name))
);

In this second example, we've created a check constraint called check_supplier_name. This constraint ensures that the supplier_name column always contains uppercase characters.

Adding a CHECK using ALTER TABLE ADD statement

The syntax for creating a check constraint in an ALTER TABLE statement is:

ALTER TABLE table_name add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

The DISABLE keyword is optional. If you create a check Oracle constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For example:

ALTER TABLE table_name add CONSTRAINT check_table_name    CHECK (column_name IN ('OARCLE', 'ORALCE10g', 'Oracle 9i'));

In this example, we've created a check constraint on table called check_table_name that ensures that field column_name only contains the following values:  ('OARCLE', 'ORALCE10g', 'Oracle 9i'. In oder cases the PL/SLQ return an Oracle EXCEPTIONS.