1 – 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 restrictied, 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 (see the UML page for broader coverage).
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: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY constraints. You also have an INDEX, which is an implementation structure that lets you impose a unique constraint across all rows in a table; and a TRIGGER, which is an implementation structure that lets you apply programmatic rules that restrict column values.
