Oracle SQL check constrain

When developing an application developers do check if the input provided by the user is valid and can be stored in a database. This is a good practice as you want to make sure the data that is entering your database is consistent and correct. Data consistency can make or brake your application and having inconsistent data in your dataset will come back at you at some point in time. Also checking if only valid data is entered in your database is part of a deep application boundary validation security approach which I discussed in a previous post where the insert statement in your database is the last part of a chain of security checks.

In large projects you commonly have application developers and you will have developers architects who will be responsible for the datamodel. You can leave the validation that the correct data is entered in your database with the application developers however it is good practice to also have a check on your database level. Making sure such a check is in place is the responsibility of a data-model architect. For this we can make use of Oracle database check constrains. A check constrain will check at the moment the insert is done into a table if the data in the insert statement is in line with the check.

It might look a little overdone to have a dual check, one in the application and one in the database however it can ensure that all applications are entering consistent data and ensure that a application developer is bound to follow the rules set by the data-model architect. Secondly it will help when you will have multiple applications all entering data into a single database to ensure they all play by the same rules.

As an example we have a table for international sourcing of products which we call INT_PROD_SOURCE.  For this we have stated that the source_country code always need to be in uppercase characters. The application is in principle responsible of ensuring this however we also have enabled a check on database level. As you can see we have a constraint on source_country and we do a check on the same.

CREATE TABLE INT_PROD_SOURCE
(
  prod_id numeric(10),
  sup_id numeric(10),
  source_country varchar2(4),
  CONSTRAINT source_country
  CHECK (source_country = upper(source_country))
);

The constrain and check can be defined at creation time of the table. There are a lot of things you can enter in such a check for example you can use a IN check as shown below this ensures that only records can be entered that will have a source_country code that is NL, DE or BE;

CHECK (source_country IN ('NL', 'DE', 'BE'));

One things is not permited, which is a shame, and that is a sub-query. This is really a shame however with some good programming you can find your way around this.