All You Really Need To Know About Constraints

Not long ago I had a conversation with another team member (non-DBA), and had to explain in easy to understand terms some information about constraints.
In order to provide accurate information, I had to look up the documentation to refresh my memory. I needed to explain the difference between deferrable and not deferrable constraints.

This got me thinking, I must not be the only DBA, who doesn’t remember exactly what a deferrable constraint is.

Do you feel it has been a long time since you took a DBA course? Do you feel sometimes that you forgot basic information about some Oracle objects?
You are not the only one! Most of us don’t work every day, with all the things we learned. Some of the knowledge (stuff we don’t use on daily basis) goes out of the short term memory, and needs a refresher.

Look no further, today’s post will give you a refresher on Oracle constraints!

1. What Are Constraints?
2. How Many Types Of Constraints Are There?
3. What Are The Possible States Of Constraints?

1. What Are Constraints?

Constraints are rules created on columns, that will allow, or will not allow a value in the database. Usually these rules come from the business, and are translated into the database world.
Example of rules:

• each employee must have a LASTNAME
• salary column must be greater than 0
• each employee’s ID must be a unique number
• each employee’s manager, must be an employee

2. How Many Types Of Constraints Are There?

There are 6 types of constraints:

• NOT NULL Constraint
  Does not allow NULL values to be inserted into a column. In other words, each row, needs to have a value in the column.
• Unique Constraint
  Does not allow duplicates in the column/columns.
  You can create a unique constraint on a combination of columns, which is called a composite unique key.
  The interesting part of the unique constraint is the fact that it allows nulls in the column. You can have two rows with null values, and it is totally okay.
  Also the unique constraint creates a unique index on the column(s).
• Primary Key Constraint
  The easiest way to remember the rules for a primary key, is that the column must contain data (cannot be null), and must be unique.
  Basically it is a combination of the first two constraints. Primary keys can also be created on multiple columns, and in this case they are called composite primary keys.
  If a unique index already exists when creating the primary key, Oracle will use that index. If not, then Oracle will create a unique index.
  You can have only one primary key on a table.
• Foreign Key Constraint
  It creates a relationship between a column, or set of columns, and a primary key or unique key. The table where you define the foreign key constraint is called the child table.
  The table that the foreign key is referencing is called the parent table. When a value is inserted into a column that has a foreign key, it has to either reference a valid value, or be null.
• Check Constraint
  It defines rules for the column that each row must comply with.
• REF Constraint
  It describes the relationship between a column of type REF, and the object it references. I personally have not worked with this type of constraint.

3. What Are The Possible States Of Constraints?

Constraints can be created either DEFERRABLE or NOT DEFERRABLE.
This state of constraints cannot be changed with an alter statement. If the state of a constraint needs to be changed, then the constraint needs to be dropped and recreated.

What you need to remember about these two states:
DEFERRABLE – if a constraint is deferrable, this means you can use the SET CONSTRAINT statement to defer or postpone the checking of the constraint to the end of the transaction.
NOT DEFERRABLE – this is the default behaviour. The constraint is checked right away. If the value doesn’t comply with the constraint, an error is returned and the transaction is not committed.

Constraints can also be in a state of ENABLED or DISABLED.
If a constraint is ENABLED then the constraint is active, meaning the constraint is validated when a new row is inserted.
If a constraint is DISABLED, then Oracle will not validate data against the constraint.
This means you can insert a null value into a not null column, if the not null constraint is disabled.

When you enable a constraint, you have 2 options: enable the constraint and validate the existing data: VALIDATE.
If the existing data does not comply with the constraint, then you cannot enable the constraint.

Or, enable the constraint, and do not validate the existing data: NOVALIDATE.
In this case, Oracle will not check the existing data, and it will enforce the constraint for any new data.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana