Difference Between Inline and Out-of-Line Constraints

Constraints on tables and columns allow you to enforce the data quality. In SQL, there are two ways of creating constraints on a table: inline and out of line.

In this article, I am going to explore these constraints and the advantages they have, as well as explain which one I recommend and why.

What is an Inline Constraint?

An inline constraint is a constraint you declare on the same line as the column when creating a table.

CREATE TABLE employee (
emp_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR2(200),
last_name VARCHAR2(200),
dept_id NUMBER(10)
);

In this example, the words PRIMARY KEY after the emp_id column indicate that emp_id is the primary key.

Thus, we have created a primary key constraint on this column by adding the keywords. The concept is the same regardless of the constraint type.

What is an Out-of-Line Constraint?

An out-of-line constraint is the constraint declared on a separate line for the column. We add it at the end of the CREATE TABLE statement.

For example, we have the following script:

CREATE TABLE employee (
emp_id NUMBER(10),
first_name VARCHAR2(200),
last_name VARCHAR2(200),
dept_id NUMBER(10),
CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

As you can see, we set the PRIMARY KEY constraint, called pk_emp, to the emp_id column at the end of the statement.

This concept works in the same way regardless of the constraint type.

Now, let us analyze the difference between these two types of constraints, other than where they are declared.

Out-of-Line Constraints Can Have Names Specified

When creating out-of-line constraints, we can specify a name. While this might seem a waste of time, it can be helpful.

Consider this on a particular example:

CREATE TABLE employee (
emp_id NUMBER(10),
first_name VARCHAR2(200),
last_name VARCHAR2(200),
dept_id NUMBER(10),
CONSTRAINT pk_emp PRIMARY KEY (emp_id),
CONSTRAINT fk_emp_deptid FOREIGN KEY (dept_id) REFERENCES department (dept_id),
CONSTRAINT ck_emp_lnlen CHECK (LENGTH(last_name) > 3)
);

We have specified the following names for a few constraints:

• pk_emp
• fk_emp_deptid
• ck_emp_lnlen

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:

CREATE TABLE employee (
emp_id NUMBER(10),
first_name VARCHAR2(200),
last_name VARCHAR2(200) NOT NULL,
dept_id NUMBER(10)
);

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

CREATE TABLE employee (
emp_id NUMBER(10),
first_name VARCHAR2(200),
last_name VARCHAR2(200),
dept_id NUMBER(10),
CONSTRAINT nn_emp_ln NOT NULL (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:

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:

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.