Check Constraint In Oracle

In Oracle, a check constraint is a rule defined on a column or a set of columns in a table to enforce data integrity. It ensures that values entered into the column(s) meet specified criteria or conditions. Here's how you can define a check constraint in Oracle:-

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK (condition);

Let's break down the components:-

- ALTER TABLE table_name: This specifies the table on which you want to add the constraint.

- ADD CONSTRAINT constraint_name: This defines the name of the check constraint.

- CHECK (condition): This specifies the condition that must be satisfied for the data entered into the column(s).

Here's an example of creating a check constraint in Oracle:-

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    salary NUMBER,

    CONSTRAINT check_salary CHECK (salary > 0)

);

In this example, the check_salary constraint ensures that the salary column accepts only positive values.

You can also add a check constraint to an existing table using the ALTER TABLE statement:-

ALTER TABLE employees

ADD CONSTRAINT check_salary CHECK (salary > 0);

Let's create a table in Oracle with a check constraint and demonstrate its usage with an example:-

-- Create table with a check constraint

CREATE TABLE students (

    student_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    age NUMBER,

    CONSTRAINT check_age CHECK (age >= 18)

);

In this example, we have a table named students with columns student_id, first_name, last_name, and age. The age column has a check constraint named check_age, which ensures that the age entered is equal to or greater than 18.

Let's insert some sample data into the students table:-

-- Inserting data violating the check constraint

INSERT INTO students (student_id, first_name, last_name, age) VALUES (1, 'John', 'Doe', 17);

Since the inserted age value (17) violates the check constraint, Oracle will raise an error:-

ORA-02290: check constraint (SCHEMA_NAME.CHECK_AGE) violated

Now, let's insert data that satisfies the check constraint:-

-- Inserting data satisfying the check constraint

INSERT INTO students (student_id, first_name, last_name, age) VALUES (2, 'Jane', 'Smith', 20);

This insertion will succeed without any error, as the age value (20) meets the condition specified in the check constraint.

To verify the data in the table, you can execute a simple select query:-

-- Selecting data from the students table

SELECT * FROM students;

Output:

STUDENT_ID  FIRST_NAME  LAST_NAME  AGE

--------------------------------------

2           Jane        Smith      20

As shown in the output, only the data that satisfies the check constraint is inserted into the students table, ensuring data integrity and consistency.

Check constraints in Oracle are essential for maintaining data integrity by enforcing business rules and validation criteria at the database level. They help prevent the insertion of invalid or inconsistent data into the database tables.

Here are five frequently asked questions about check constraints in Oracle:-

1. What is a check constraint in Oracle?

   - A check constraint in Oracle is a rule defined on a column or set of columns in a table to enforce data integrity by ensuring that values entered into the column(s) meet specific criteria or conditions.

2. How do I create a check constraint in Oracle?

   - You can create a check constraint in Oracle using the ALTER TABLE statement to add the constraint to an existing table, or within the CREATE TABLE statement when creating a new table.

3. What types of conditions can I specify in a check constraint?

   - Check constraints in Oracle allow you to specify a wide range of conditions, including comparisons (>, <, =), logical operators (AND, OR), arithmetic expressions, and even calls to user-defined functions.

4. Can I disable or drop a check constraint in Oracle?

   - Yes, you can disable or drop a check constraint in Oracle using the ALTER TABLE statement. Disabling a constraint temporarily suspends its enforcement, while dropping a constraint removes it permanently from the table.

5. Are check constraints only for numeric values?

   - No, check constraints in Oracle can be applied to various data types, including numeric, character, date, and Boolean. You can define conditions based on the data type of the column to enforce specific rules or validat criteria.