Sem 3‎ > ‎DBMS (SQL) LAB‎ > ‎

Prep: Constraints

posted Nov 10, 2011, 12:47 AM by Neil Mathew   [ updated Nov 10, 2011, 12:47 AM ]

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_IdLastNameFirstNameAddressCity
    1HansenOlaTimoteivn 10Sandnes
    2SvendsonToveBorgvn 23Sandnes
    3PettersenKariStorgt 20Stavanger

    The "Orders" table:

    O_IdOrderNoP_Id
    1778953
    2446783
    3224562
    4245621

    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)
    REF
    ERENCES 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
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.


CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
);


CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

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.

  • DEFAULT

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.


CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)



Comments