Constraints
To ensure that the contents of columns fulfill your database requirements, specify constraints.
Ordinary (enforced) constraints are always checked at the end of every statement that modifies the table. If the constraint is violated, an error is returned and the statement is aborted. If the statement is within a multi-statement transaction, the transaction is not aborted.
Constraints can also be declared as NOT ENFORCED. This allows the database designer to describe a constraint (such as a referential relationship) without the overhead of checking the constraint. The assumption is that the constraint will be enforced externally in some way, and so the DBMS does not have to do it. The constraint description is available to the query optimizer and to external query generators, allowing better query plans or better queries to be generated. If the actual data violates the non-enforced constraint, incorrect results are possible.
Constraints can be specified for individual columns or for the entire table. For details, see
Table-level and Column-level Constraints.
The types of constraints are:
• Unique constraint–Ensures that a value appears in a column only once. Unique constraints are specified using the UNIQUE option.
• Referential constraint–Ensures that a value assigned to a column appears in a corresponding column in another table. Referential constraints are specified using the REFERENCES option.
• Primary key constraint–Declares one or more columns for use in referential constraints in other tables. Primary keys must be unique.