SQL Constraints

Constraints are rules placed on one or more columns of a table to limit the type of data that can go into a table. Constraints ensure the integrity and the accuracy of the data inside the database.

Constraints can be either a column level or a table level. The column level constraints apply to a column, whereas the table level constraints apply to the whole table.

There are different types of SQL constraints, in the following are some of the most commonly used constraints available:

• NOT NULL - Ensures that a column cannot have a NULL value.
• PRIMARY KEY - Uniquely identifies each row in a table.
• UNIQUE - Ensures that all values in a column are different.
• DEFAULT - Provides a default value for a column when no value is specified.
• FOREIGN KEY - Uniquely identifies a row in another table.
• CHECK - Ensures that all values in a column satisfies a certain condition.
• INDEX - Used to create and fetch data from the database very fast.

SQL Create Constraints

Constraints can be specified during the creation of the table using the CREATE TABLE statement or after the creation of the table using the ALTER TABLE statement.

Syntax

The syntax for specifying constraints during and after the creation of the table is as follows:

• During the creation of the table with the CREATE TABLE statement:

  CREATE TABLE table_name (
      column1 data_type constraint,
      column2 data_type constraint,
      ...
  );
  

• After the creation of the table with the ALTER TABLE statement:

  ALTER TABLE table_name 
      ADD constraint_name (column_name, ....);
  

Examples

• During the creation of the table

  The following SQL statement will add a NOT NULL constraint to theid column during the creation of the "Person" table:

  CREATE TABLE person (
    id INT NOT NULL,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    phone VARCHAR(15)
  )
  

• After the creation of the table

  The following SQL statement will add a UNIQUE constraint to the phone column after the creation of the "Person" table:

  ALTER TABLE person
      ADD CONSTRAINT my_unique_constraint  UNIQUE(phone);
  

SQL Drop Constraints

Any defined constraint can be removed using the ALTER TABLE command with the DROP CONSTRAINT option.

The following SQL statement will drop the primary key constraint from the "Person" table.

ALTER TABLE person 
    DROP CONSTRAINT person_pk;

Some database systems implementations provide a possibility a disable constraints, so instead of dropping a constraint from a database, you can temporarily disable the constraint and then enable it later.