SQL Constraints

Constraints are used to limit the type of data that can go into a table.

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

(Note that I am aware that most of these examples are not suited for Oracle.)

We will focus on the following constraints:

• NOT NULL
  
  

  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.
  
  
  

• UNIQUE
  
  

  SQL 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.
  
  
  (to add UNIQUE CONSTRAINT using Alter table)

  ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

  
  
• PRIMARY KEY
  
  

  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.

  Each table should have a primary key, and each table can have only ONE primary key.

  
  
  
• FOREIGN KEY
  
  

  A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

  Let's illustrate the foreign key with an example. Look at the following two tables:

  The "Persons" table:

  P_Id	LastName	FirstName	Address	City
  1	Hansen	Ola	Timoteivn 10	Sandnes
  2	Svendson	Tove	Borgvn 23	Sandnes
  3	Pettersen	Kari	Storgt 20	Stavanger

  The "Orders" table:

  O_Id	OrderNo	P_Id
  1	77895	3
  2	44678	3
  3	22456	2
  4	24562	1

  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 that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

  
  

  
  

  CREATE TABLE Orders

  (

  O_Id int NOT NULL,

  OrderNo int NOT NULL,
  
  P_Id int,

  PRIMARY KEY (O_Id),

  FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

  )

  
  

  CREATE TABLE Orders
  (
  O_Id int NOT NULL PRIMARY KEY,
  OrderNo int NOT NULL,
  P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
  )

  
  

  CREATE TABLE Orders
  (
  O_Id int NOT NULL,
  OrderNo int NOT NULL,
  P_Id int,
  PRIMARY KEY (O_Id),
  CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
  REFERENCES Persons(P_Id)
  )

  
  

  ALTER TABLE Orders

  ADD FOREIGN KEY (P_Id)

  REFERENCES Persons(P_Id)

  
  

  To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

  
  

  ALTER TABLE Orders

  ADD CONSTRAINT fk_PerOrders

  FOREIGN KEY (P_Id)

  REFERENCES Persons(P_Id)

  
  

• CHECK
  

• DEFAULT