Summary: in this tutorial, you will learn about SQL constraints that allows you to enforce the integrity of data automatically.
Introduction to SQL constraints
SQL constraints or just constraints are rules that you define which data values are valid while doing INSERT, UPDATE, and DELETE operations. When constraints are in place, SQL engine rejects all the transactions that break the rules therefore constraints help you enforce the integrity of data automatically.
SQL constraints scope
SQL constraints can be applied at the table or column level.
- Table-level constraints: you declare table-level constraints that apply to one or more columns. Table-level constraints are declared independently from the column definition. You often declare table-level constraints at the end of the CREATE TABLE statement.
- Column-level constraints: you declare column-level constraints when you define columns for the table. The column-level constraint is applied particularly to the column where it attached to.
Declaring SQL constraints
The following illustrates the general syntax of constraints:
CONSTRAINT [name] type [(column1, column2…)] [predicate] [deferment] [deferment_timing]
Let’s examine the syntax in greater detail:
- name: is the name of constraint. It is an optional part. If you omit the name of constraint, the database system will create a name automatically for you. In some database systems, you can also omit the CONSTRAINT keyword when declaring a constraint.
- type: is valid type of constraint such as PRIMARY KEY, FOREIGN KEY, UNIQUE and CHECK. If the database system provides a specific type, you can specify it here.
- column1, column2…: you attach constraint to one or more columns separated by a comma. The column list is used only for table-level constraint.
- predicate: is only applicable for the CHECK constraint. You can declare a predicate for CHECK constraints in this clause.
- deferment: is accept either DEFERABLE or NOT DEFERABLE. If a constraint is deferable, the database system checks rules at the end of a transaction. When you declare a constraint which is NOT DEFERABLE, the database system checks rules for each SQL statement in a transaction.
- deferment_timing: accepts INITIALLY IMMEDIATE or INITIALLY DEFERRED to specify the default time to check the rules if a constraint is DEFERABLE.
- If the deferment_timing is INITIALLY IMMEDIATE, the constraint is checked after each statement. This is the default value for deferment_timing
- If the deferment_timing is INITIALLY DEFERRED, the constraint is checked only at the end of the transaction.
Notice that different database system implements constraints with variations so please read the documentation for more information on constraints on a specific platform.
In this tutorial, we have discusses about the SQL constraints, its scope and detailed syntax.