This chapter covers some specific constraints that are part of the CREATE TABLE syntax.
NOT NULL
NOT NULL states that a column requires a value. If an insert or update operation fails to provide a value for the column, it will raise an error.
NOT NULL is exclusively a column constraint.
PRIMARY KEY
The PRIMARY KEY constraint defines a primary key for the table. Only one PRIMARY KEY constraint is permitted for a table. The PRIMARY KEY constraint is unique and required by definition, so a specific UNIQUE or NOT NULL constraint is not necessary.
If the primary key is only one column, then it should be defined as a column constraint. If the table has a composite primary key (more than one column to uniquely define a row), then it must be defined as a table constraint.
Suppose we have a library database, and want to model a many-to-many relationship between books and patrons. We might have a books table, a patrons table, and a loans table to associate the two. We could use a composite primary key in the loans table, since a book and a patron uniquely identify a loan. The key structure of our tables would look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE books ( id serial PRIMARY KEY ) CREATE TABLE patrons ( id serial PRIMARY KEY ) CREATE TABLE loans ( book_id integer, patron_id integer, PRIMARY KEY (book_id, patron_id) ) |
In this case, both the books and the patrons table use the column version of the PRIMARY KEY constraint, while the loans table uses the table version, since it has a composite key.
CHECK
When an insert or update operation is attempted on the column, CHECK calculates expression and ensures that it does not evaluate to false (if expression evaluates to either true or NULL the check will succeed). If it evaluates to false, the operation will cancel and raise an error.
It is best practice to define CHECK constraints involving only a single column as column constraints. For example, suppose we create this table:
|
1 2 3 4 5 |
CREATE TABLE states ( state_code integer CHECK (state_code BETWEEN 1 AND 50) ); |
Note that we’re using the column constraint form here, since expression references no column but its own.
With this CHECK constraint in place, if we attempt to do this:
|
1 2 3 |
INSERT INTO states VALUES (51); |
We will get this response:
“states_state_code_check”
DETAIL: Failing row contains (51).
It is best practice to define CHECK constraints involving multiple columns as table constraints. For example, suppose we have a products table, and we want to ensure a minimum profit margin of 40 percent. The table definition might look like this:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE products ( id serial PRIMARY KEY, name text NOT NULL, cost numeric(6,2) NOT NULL, price numeric(6,2) NOT NULL, CHECK (1 - cost / price >= .4) ) |
An INSERT or UPDATE that attempts to create a combined cost and price value that returns false when the CHECK formula is applied will cancel the operation and raise an error.
N.B. if a referenced column has a null value in it, the CHECK constraint will pass, because the result of the formula will be NULL rather than false. So, for a CHECK constraint to work as intended, it is generally necessary either to add the NOT NULL constraint to the two columns that the formula references (preferable, since if one of the values is missing, the resulting error will point specifically to that problem) or check for NOT NULL in the formula: CHECK (1 - cost / price >= .4 AND 1 - cost / price IS NOT NULL).
Regular expressions in CHECK constraints
CHECK constraints support regular expressions in most RDBMSs. The syntax varies. These are the syntaxes for four popular RDBMSs:
| RDBMS | Syntax |
|---|---|
| PostgreSQL | ~ ‘expression’ |
| MySQL | regexp ‘expression’ |
| Oracle | REGEXP_LIKE(field_name, ‘expression’) |
| Microsoft SQL Server | (not supported) |
For example, using PostgreSQL, CHECK (column ~ '^[A-Z]{8}$') will require a value in columnto consist of eight uppercase alpha characters.
DEFAULT
DEFAULT provides a value for a column when none is given in an insert operation (or an update operation sets the value to NULL). The given value must match the column’s data type. If a column has no DEFAULT constraint, its default value is NULL.
DEFAULT is exclusively a column constraint.
UNIQUE
The UNIQUE constraint ensures that an inserted or updated value for a column or set of columns does not duplicate the value or values in any other row of the table. If there is more than one column in the column list, then the UNIQUE constraint fails if the proposed values for all of the columns in the list are the same as the set of values for the same columns in an existing row. In other words, in the case of a multi-column UNIQUE constraint, some of the columns can be duplicates of other rows, but not all of them can be.
The UNIQUE constraint creates an index on the column or columns specified in the constraint. Without an index, a large table could take quite a long time to check all the rows for a duplicate — several hours with a very large table.