Summary: In this tutorial, you will learn about SQL constraints, which allow you to enforce the integrity of data automatically.
Introduction to SQL constraints
SQL constraints, or just constraints, are rules that define which data values are valid while performing INSERT, UPDATE, and DELETE operations. When constraints are in place, the SQL engine rejects all transactions that break the rules; therefore, constraints help enforce the integrity of data automatically.
ANSI SQL provides four types of constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK. In the next tutorials, we will explore each kind of constraint.
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 it is attached to.
Declaring SQL constraints
The following illustrates the general syntax of constraints:
CONSTRAINT [name] type [(column1, column2…)]
Code language: CSS (css)[deferment_timing]
Let’s examine the syntax in greater detail:
- name: is the name of the constraint. It is an optional part. If you omit the name of the 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 a valid type of constraints 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 the 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 DEFERRABLE or NOT DEFERRABLE. If a constraint is deferrable, the database system checks rules at the end of a transaction. When you declare a constraint that is NOT DEFERRABLE, 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 DEFERRABLE.
- 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 systems implement constraints with variations, so please read the documentation for more information on constraints on a specific platform.
In this tutorial, we have discussed the SQL constraints, its scope, and detailed syntax.
