SQL Constraints

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.

ANSI SQL provides four types of constraints including PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK. We will explore each kind of constraint in the next tutorials.

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…)] 
           Code language: CSS (css)

[predicate] [deferment]

[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 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 which 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 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 the SQL constraints, its scope, and detailed syntax.