Database Constraints

Database Constraints

Database constraints are user-defined structures that let you restrict the behaviors of columns. There are five principal types of constraints covered in this web page.

You can create these constraints when you define a table with a SQL CREATE statement. A constraint defined on the same line as its column is called an inline constraint, while a constraint on its own line in a CREATE statement is an out-of-line constraint. Out-of-line constraints must reference (or point to) the column that they constrain. You can also add, modify, or drop these constraints with an ALTER statement after you’ve created the table.

While all databases support the set of possible constraints. They sometimes provide system specific alternatives. The following chart describes the types of restrictions you can place on columns and tables by using database-level constraints and triggers.

A column-level constraint may restrict the minimum cardinality of a column. When a column is restricted, any INSERT or UPDATE statements must provide a value for that column to add or change a row respectively.

Variations on the generalized concept of a constraint are found in the NOT NULL and CHECK constraints. A NOT NULL can universally impose a minimum cardinality constraint on a column. That means you must provide a value to the constrained column with any INSERT or UPDATE statement against the table. A CHECK constraint can sometimes (implementation dependent) let you impose a minimum cardinality constraint on a collection of columns.

The data type of a column generally sets the maximum cardinality of a column. For example, a scalar variable can only hold one thing. On the other hand a collection may hold many things.

Cardinality is the combination of the minimum and maximum cardinality, respectively the least number of things in a column or relationship and the most number of things in the same column or relationship. Cardinality is represented by numbers for the minimum and maximum cardinality separated by two dots. For example, ANSI compliant columns are nullable, or have a zero to one cardinality, 0..1 .

As mentioned, scalar data types limit the maximum cardinality to one, which means a scalar column’s cardinality is 0..1 when the column is nullable. A nullable column exists when you can create or modify the column to let it contain a null value. A column value is thereby optional. This is supported by some IDE tools. The cardinality of a not null column is constrained to require a value and is represented by 1..1 cardinality. Not null columns are mandatory because they require you to provide a value.

Collection data types set the maximum cardinality to many (dependent on the implementation, this may be a bounded or an unbounded many). The cardinality for an optional collection data type column is 0..*, while the not null collection data type column is 1..*.

There are five types of database constraints. They are:

CHECK Constraint ↓

CHECK constraints are table-level constraint. You can only create table-level constraints as out-of-line constraints. You typically restrict a column value with a CHECK constraint to a set of values defined by the constraint. A CHECK constraint doesn’t make the column mandatory, which means the default is the ANSI standard for nullable columns. A cardinality of 0..1 (optional) is the ANSI standard. You override it by providing a NOT NULL constraint. The NOT NULL constraint makes the column cardinality 1..1, or mandatory.

Another form of a CHECK constraint converts null values to default values. The DEFAULT clause when available in an implementation acts is an inline constraint. It imposes a 1..1 cardinality on a column.

FOREIGN KEY Constraint ↓

A FOREIGN KEY constraint restricts the values that are acceptable in a column or group of columns to those values found in a listing of the column or group of columns used to define the primary key. Dependent on the implementation, this may or may not impose a NOT NULL column constraint on all members of the foreign key. If the implementation make the column or set of columns mandatory, then it makes the cardinality of columns in the FOREIGN KEY mandatory, or 1..1 to the PRIMARY KEY. However, the default is that a FOREIGN KEY is 0..1 to the PRIMARY KEY, which means that a row may be inserted in the table with the FOREIGN KEY column. At least, it can be done provided that the constrained column or set of columns are null values.

NOT NULL Constraint ↓

A NOT NULL constraint restricts a column by making it mandatory. This means you can’t insert a row in the table without providing a valid data type value for all NOT NULL constrained columns. A mandatory column has the cardinality of 1..1.

PRIMARY KEY Constraint ↓

A PRIMARY KEY constraint checks whether a column value will be unique among all rows in a table and disallows null values. Therefore, a PRIMARY KEY has the behaviors of both NOT NULL and UNIQUE constraints. A PRIMARY KEY may span two or more columns. A multiple column PRIMARY KEY is known as a composite or compound key, which can be confusing but essentially the terms only mean that the key spans columns.

UNIQUE Constraint ↓

A UNIQUE constraint checks whether a column value will be unique among all rows in a table.

You may define constraints when you create tables, or defer their creation and use an ALTER TABLE command. However, the NOT NULL constraint can’t be added with the ALTER TABLE command. Oracle requires that you add a NOT NULL constraint as an inline constraint. Unfortunately, inline NOT NULL constraints often don’t receive meaningful names and often rely on system generated sequence names in Oracle. NOT NULL constraints are an attribute of a table’s column in MySQL and can’t have a name.

Inline and Out-of-line (Table-level) Constraints ↓

This shows you how to define inline and out-of-line constraints in Oracle. The approach is similar for MySQL and Microsoft® SQL Server but there are slight syntax variations. It also shows you how to define meaningful names for inline constraints.