It might seem that it’s just unnecessary typing, however, it is not. We will take a closer look at this.

So, why do we need to assign a name to a constraint?

Having named constraints can be helpful in several situations. Without specifying the name, Oracle automatically generates a name for the constraint that it does for all inline constraints. Usually, this name does not provide any useful information.

When you get errors in SQL statements, PL/SQL code, or application code, it is a good idea to use the constraint name and know what it refers to or at least make a guess. Such names as pk_emp or ck_emp_lnlen would be more descriptive than the generic EMP1290894FH name.

Also, when reviewing execution plans, the constraint name is often used in the output, which makes it easier to work out how the plan is being executed. Especially, when we have cases determining if primary key or foreign key are being used.

NOT NULL Constraints Can Only Be Declared Inline

There’s only one constraint type that can be declared as an inline constraint. This is the NOT NULL constraint.

This means that you can’t declare it as out of line.

Execute the following code:

1

2

3

4

5

6

CREATE TABLE employee(

emp_id NUMBER(10),

first_name VARCHAR2(200),

last_name VARCHAR2(200)NOTNULL,

dept_id NUMBER(10)

);

However, when running the code below, we can see that it does not work:

1

2

3

4

5

6

7

CREATE TABLE employee(

emp_id NUMBER(10),

first_name VARCHAR2(200),

last_name VARCHAR2(200),

dept_id NUMBER(10),

CONSTRAINT nn_emp_ln NOTNULL(last_name)

);

To sum it up, for the NOT NULL constraints, we must declare them inline.

CHECK Constraints Can Refer to Multiple Columns

If you create a CHECK inline constraint, it can only refer to the column it’s being created on.

However, if you create a CHECK constraint as out of line, it can refer to multiple columns.

Create the employee table with the CHECK constraint as shown below:

1

2

3

4

5

6

CREATE TABLE employee(

emp_id NUMBER(10),

first_name VARCHAR2(200)CHECK(LENGTH(first_name)>10),

last_name VARCHAR2(200),

dept_id NUMBER(10)

);

This constraint shows that first_name must exceed 10 characters long.

However, what if we wanted to specify that the combination of first name and last name must exceed 10 characters?

To do this, re-write the code as an out-of-line constraint:

1

2

3

4

5

6

7

CREATE TABLE employee(

emp_id NUMBER(10),

first_name VARCHAR2(200),,

last_name VARCHAR2(200),

dept_id NUMBER(10),

CONSTRAINT ck_fullname_len CHECK(LENGTH(first_name||last_name)>10)

);

We can see that this rule can be implemented only with an out-of-line constraint.

Recommended Method

Having analyzed both methods: inline or out of line, I recommend using the out-of-line constraints.

There are a few reasons for this.

First, you can specify a name for your constraints and see them in error messages and internal execution plans. It can also help with disabling and enabling constraints.

Secondly, check constraints allow you to refer to multiple and single columns. Thus, it’s more flexible if you add them as an out-of-line constraint.

Finally, having all constraints declared as out of line (except NOT NULL which can only be defined as an inline constraint) makes it easier to look at your CREATE TABLE syntax and see all your constraints in one place. It’s important especially in the cases when there are large tables with many constraints.

In conclusion, you can create constraints using two different methods: inline and out of line. I recommend using the out-of-line method where you can, because there’s more flexibility, and setting a name to the constraints, thus, simplifying the analysis of your execution plans and other SQL information.

Ben Brumm is a software consultant with over 10 years experience in the IT industry. He is also the founder of DatabaseStar.com, a website that teaches database developers how to improve their skills and their career.

Ben Brumm is a software consultant with over 10 years experience in the IT industry. He is also the founder of DatabaseStar.com, a website that teaches database developers how to improve their skills and their career.