SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

i. NOT NULL Constraint:

• The NOT NULL constraint enforces a column to NOT accept NULL values.
• The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

• Example:

  The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

  CREATE TABLE PersonsNotNull
  (
  P_Idint NOT NULL,
  LastNamevarchar(255) NOT NULL,
  FirstNamevarchar(255),
  Address varchar(255),
  City varchar(255)
  )
  

ii. UNIQUE Constraint:

• The UNIQUE constraint uniquely identifies each record in a database table.
• The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
• Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

• Example:

  The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

  CREATE TABLE Persons
  (
  P_Idint NOT NULL UNIQUE,
  LastNamevarchar(255) NOT NULL,
  FirstNamevarchar(255),
  Address varchar(255),
  City varchar(255)
  )
  

iii. PRIMARY KEY Constraint:

• The PRIMARY KEY constraint uniquely identifies each record in a database table.
• Primary keys must contain UNIQUE values.A primary key column cannot contain NULL values.
• Most tables should have a primary key, and each table can have only ONE primary key.

• Example:

  The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

  CREATE TABLE Persons
  (
  P_Idint NOT NULL,
  LastNamevarchar(255) NOT NULL,
  FirstNamevarchar(255),
  Address varchar(255),
  City varchar(255),
  PRIMARY KEY (P_Id)
  )
  

iv. FOREIGN KEY Constraint:

• A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
• Look at the following two tables:
  • The "Persons" table:

• The “Orders” table:

• Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
• The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
• The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

• Example:

  The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:

  CREATE TABLE Orders
  (
  O_Idint NOT NULL,
  OrderNoint NOT NULL,
  P_Idint,
  PRIMARY KEY (O_Id),
  FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
  

  • CHECK Constraint:

  • The CHECK constraint is used to limit the value range that can be placed in a column.

  • If you define a CHECK constraint on a single column it allows only certain values for this column.
  • If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

  • Example:

    The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

    CREATE TABLE Persons
    (
     P_Idint NOT NULL,
     LastNamevarchar(255) NOT NULL,
     FirstNamevarchar(255),
     Address varchar(255),
     City varchar(255),
     CHECK (P_Id>0)
    )
    

  • DEFAULT Constraint:

  • The DEFAULT constraint is used to insert a default value into a column.

  • The default value will be added to all new records, if no other value is specified.

  • Example:

    The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

    CREATE TABLE Persons
    (
    P_Idint NOT NULL,
    LastNamevarchar(255) NOT NULL,
    FirstNamevarchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
    )