If the data adheres to the expression rules, the database engine accepts the data and permits the insert or update statement. Otherwise, if the data fails to comply with the laws, the database engine rejects the data and denies the insert or update operation.
A single column can have one or more check constraints, provided that they do not conflict with the other existing check constraints. In addition, the order of expression evaluation should not be assumed.
Similarly, you can have more than one column with a check constraint.
Rules of a Check Constraint in Oracle
When defining a check constraint in Oracle databases, make sure to follow these rules:
- The check constraint is defined on an Oracle table, not a view.
- The check constraint must only refer to the columns in that table and not in foreign tables.
- A check constraint cannot be defined in a subquery.
- A check constraint expression cannot include non—deterministic functions, user-defined functions, nested tables, pseudo columns, or nested attributes.
Although its recommended to use a check constraint during table creation, you may encounter such scenarios where you need to add a check constraint to an existing table.
Let us discuss how we can do this in an Oracle database.
Add a Check Constraint Statement
To add a check constraint to an existing database table, we use the ALTER TBALE ADD CONSTRAINT statement. The syntax is as shown:
ADD CONSTRAINT constraint_name CHECK(check_constraint_expression);
Add a Check Constraint to the Existing Table
Let us demonstrate how to add a check constraint to an existing table. Let us start by creating a test table:
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(255),
stock_quantity NUMBER,
stock_level VARCHAR2(255)
);
The previous query creates a table to store the product inventory information. Next, add some sample data as shown in the following:
INSERT INTO product_stock(product_id, product_name, stock_quantity, stock_level) VALUES (2, 'product2', 13, 'In');
INSERT INTO product_stock(product_id, product_name, stock_quantity, stock_level) VALUES (3, 'product3', 160, 'Out');
INSERT INTO product_stock(product_id, product_name, stock_quantity, stock_level) VALUES (4, 'product4', 10, 'In');
INSERT INTO product_stock(product_id, product_name, stock_quantity, stock_level) VALUES (5, 'product5', 320, 'In');
Resulting Table
Suppose we want to ensure that the inserted data has a stock_quantity of more than 100. We can add a check constraint as follows:
ADD CONSTRAINT validate_stock CHECK (stock_quantity >= 100);
Now, if we attempt to add a row with the stock_quantity of less than 100, we get an error as follows:
Error
Conclusion
There you have it! A way of adding a check constraint to an existing table in Oracle databases. We hope that you found this article helpful.
