Using Check Constraints in Oracle

Isaac
Posted by By Isaac 1 year Ago 4 Min Read

This article is written about how to use the check constraints in Oracle with syntax and examples.

What is a check constraint in Oracle?

A take a look at constraint allows you to specify a circumstance on each row in a table.

Note

A check constraint can NOT be described on a SQL View. The take a look at constraint described on a desk must refer to only columns in that table. It can now not refer to columns in other tables. A take a look at constraint can NOT consist of a SQL Subquery. A take a look at constraint can be defined in either a SQL CREATE TABLE announcement or a SQL ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a take a look at constraint using a CREATE TABLE assertion in Oracle 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 key-word is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, however the situation will now not be enforced.

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 have created a take a look at constraint on the suppliers desk called check_supplier_id. This constraint ensures that the supplier_id field carries values between a hundred 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 got created a test constraint called check_supplier_name. This constraint ensures that the supplier_name column usually consists of uppercase characters.

Using an ALTER TABLE statement

The syntax for growing a take a look at constraint in an ALTER TABLE declaration in Oracle is:

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

The DISABLE key-word is optional. If you create a test constraint the usage of the DISABLE keyword, the constraint will be created, however the situation will now not be enforced.

Example

ALTER TABLE suppliers
ADD CONSTRAINT check_supplier_name
  CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

In this example, we’ve got created a test constraint on the present suppliers desk known as check_supplier_name. It ensures that the supplier_name discipline solely contains the following values: IBM, Microsoft, or NVIDIA.

Drop a Check Constraint

The syntax for dropping a check constraint is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

ALTER TABLE suppliers
DROP CONSTRAINT check_supplier_id;

In this example, we’re shedding a check constraint on the suppliers desk referred to as check_supplier_id.

Enable a Check Constraint

The syntax for enabling a take a look at constraint in Oracle is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE suppliers
ENABLE CONSTRAINT check_supplier_id;

In this example, we’re enabling a take a look at constraint on the suppliers table referred to as check_supplier_id.

Disable a Check Constraint

The syntax for disabling a check constraint in Oracle is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE suppliers
DISABLE CONSTRAINT check_supplier_id;

In this example, we’re disabling a take a look at constraint on the suppliers table called check_supplier_id.