Is it a good practice to use CHECK constraints to validate data during an INSERT statement

Question

Which way is considered better to validate the input of data before inserting it into a table, use CHECK constraints or use if statement? Are there better ways to validate it?

1)

DECLARE
 INVALID_DATE EXCEPTION;
...
-- Check if date is valid
IF TO_DATE(dt,'DD/MM/YYYY') > TO_DATE('10/10/2010','DD/MM/YYYY')THEN
 RAISE INVALID_DATE;
END IF;

-- Insert data only if it is valid
INSERT INTO Table VALUES(dt);

EXCEPTION
 WHEN INVALID_DATE
      THEN DBMS_OUTPUT.PUT_LINE('Date is not valid');
...

2)

...
-- Do NOT check if date is valid and let an 
-- CHECK constraint exception(CH_TABLE1) be raised if it is invalid
INSERT INTO Table VALUES(dt);

EXCEPTION
 WHEN CHECK_CONSTRAINT_VIOLATED THEN
   IF SQLERRM LIKE 'ORA-02290:%CH_TABLE1%' THEN
       DBMS_OUTPUT.PUT_LINE('Date is not valid');
   ...
   END IF;
...

score 2 · Answer 1 · answered Jul 30 '16 at 03:11

If updates/inserts can come from several applications/scripts, especially ad hoc queries, then a check constraint puts the required business logic in one place which is a good thing.

If updates/inserts come from one application/source it's probably better to do the check in the application (in this case a stored procedure).

Having a check constraint is a catch all, but it's somewhat hidden.

score 2 · Accepted Answer · edited May 23 '17 at 11:51

It depends - often a multi-layered approach is required.

For example, you may have a set of simple business rules that can be maintained directly within the database as check constraints. I only use these for simple, self-contained checks.

Example - checking a Y/N flag:

alter table example_table add constraint ck_example_table_flag
check (flag is null or flag in ('Y', 'N');

In this case, if you get any other value in this column you know the data is bad and this isn't a rule that's going to change over time. Adding this constraint will prevent any application from corrupting your data.

For more complicated business rules that may not be self-contained, for example if a date depends on the person entering the value or on the contents of another field in another table, then I would handle this in the 'application'.

Sometimes the 'application' is the database - in this case your check constraints may be in table triggers or packaged pl/sql that handles all table updates. For example:

example_app.update_data(id => 1000, value => 'foo');

If this is how your data updates are handled, there's nothing wrong with handling your business rules as check constraints, triggers with checks, or directly in the update_data procedure.

In many Enterprise environments, the application layer and the database layer will be handled by different teams. If you're in the team supporting the application layer you may find it just too painful to raise a change request for the database team to put a check constraint in place - in this case adding a bit of logic to the application layer will feel more straightforward. Conversely, the database team may find it more straightforward.

@Bohemian makes an excellent point about using check constraints to enforce data integrity when there are multiple clients.

score 1 · Answer 3 · answered Jul 30 '16 at 14:29

Another consideration in favour of using check constraints is that they can improve performance by allowing the optimiser to infer that some values cannot be present in the table.

In the presence of a constraint that limits a date column to check that:

my_col > date '2010-01-01'

... a query with a predicate such as:

my_col = date '2009-01-01'

... can determine that no rows will be returned from that table.

Is it a good practice to use CHECK constraints to validate data during an INSERT statement

3 Answers3