Skip to main content
Engineering LibreTexts

15.4: Table Constraints

  • Page ID
    92207
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Table constraints are identified by the CONSTRAINT keyword and can be used to implement various constraints described below.

    IDENTITY constraint

    We can use the optional column constraint IDENTITY to provide a unique, incremental value for that column. Identity columns are often used with the PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a column with a tinyint, smallint, int, decimal or numeric data type. This constraint:

    • Generates sequential numbers
    • Does not enforce entity integrity
    • Only one column can have the IDENTITY property
    • Must be defined as an integer, numeric or decimal data type
    • Cannot update a column with the IDENTITY property
    • Cannot contain NULL values
    • Cannot bind defaults and default constraints to the column

    For IDENTITY[(seed, increment)]

    • Seed – the initial value of the identity column
    • Increment – the value to add to the last increment column

    We will use another database example to further illustrate the SQL DDL statements by creating the table tblHotel in this HOTEL database.

    CREATE TABLE  tblHotel
    (
    HotelNo                 Int               IDENTITY (1,1),
    Name                    Char(50)          NOT NULL,
    Address                 Char(50)          NULL,
    City                    Char(25)          NULL,
    )

    UNIQUE constraint

    The UNIQUE constraint prevents duplicate values from being entered into a column.

    • Both PK and UNIQUE constraints are used to enforce entity integrity.
    • Multiple UNIQUE constraints can be defined for a table.
    • When a UNIQUE constraint is added to an existing table, the existing data is always validated.
    • A UNIQUE constraint can be placed on columns that accept nulls. Only one row can be NULL.
    • A UNIQUE constraint automatically creates a unique index on the selected column.

    This is the general syntax for the UNIQUE constraint:

    [CONSTRAINT constraint_name]
    UNIQUE [CLUSTERED | NONCLUSTERED]
    (col_name [, col_name2 […, col_name16]])
    [ON segment_name]

    This is an examle using the UNIQUE constraint.

    CREATE TABLE EMPLOYEES
    (
    EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,
    )

    FOREIGN KEY constraint

    The FOREIGN KEY (FK) constraint defines a column, or combination of columns, whose values match the PRIMARY KEY  (PK) of another table.

    • Values in an FK are automatically updated when the PK values in the associated table are updated/changed.
    • FK constraints must reference PK or the UNIQUE constraint of another table.
    • The number of columns for FK must be same as PK or UNIQUE constraint.
    • If the WITH NOCHECK option is used, the FK constraint will not validate existing data in a table.
    • No index is created on the columns that participate in an FK constraint.

    This is the general syntax for the FOREIGN KEY constraint:

    [CONSTRAINT constraint_name]
    [FOREIGN KEY (col_name [, col_name2 […, col_name16]])]
    REFERENCES [owner.]ref_table [(ref_col [, ref_col2 […, ref_col16]])]

    In this example, the field HotelNo in the tblRoom table is a FK to the field HotelNo in the tblHotel table shown previously.

    USE HOTEL
    GO
    CREATE TABLE  tblRoom
    (
    HotelNo           Int               NOT NULL ,
    RoomNo            Int               NOT NULL,
    Type              Char(50)          NULL,
    Price             Money             NULL,
    PRIMARY KEY (HotelNo, RoomNo),
    FOREIGN KEY (HotelNo) REFERENCES tblHotel
    )

    CHECK constraint

    The CHECK constraint restricts values that can be entered into a table.

    • It can contain search conditions similar to a WHERE clause.
    • It can reference columns in the same table.
    • The data validation rule for a CHECK constraint must evaluate to a boolean expression.
    • It can be defined for a column that has a rule bound to it.

    This is the general syntax for the CHECK constraint:

    [CONSTRAINT constraint_name]
    CHECK [NOT FOR REPLICATION] (expression)

    In this example, the Type field is restricted to have only the types ‘Single’, ‘Double’, ‘Suite’ or ‘Executive’.

    USE HOTEL
    GO
    CREATE TABLE  tblRoom
    (
    HotelNo           Int               NOT NULL,
    RoomNo            Int               NOT NULL,
    Type              Char(50)          NULL,
    Price             Money             NULL,
    PRIMARY KEY (HotelNo, RoomNo),
    FOREIGN KEY (HotelNo) REFERENCES tblHotel
    CONSTRAINT Valid_Type
    CHECK (Type IN (‘Single’, ‘Double’, ‘Suite’, ‘Executive’))
    )

    In this second example, the employee hire date should be before January 1, 2004, or have a salary limit of $300,000.

    GO
    CREATE TABLE SALESREPS
    (
    Empl_num          Int      Not Null CHECK (Empl_num BETWEEN 101 and 199),
    Name              Char (15),
    Age               Int               CHECK (Age >= 21),
    Quota             Money             CHECK (Quota >= 0.0),
    HireDate       DateTime,
    CONSTRAINT  QuotaCap                CHECK ((HireDate < “01-01-2004”) OR (Quota <=300000))
    )

    DEFAULT constraint

    The DEFAULT constraint is used to supply a value that is automatically added for a column if the user does not supply one.

    • A column can have only one DEFAULT.
    • The DEFAULT constraint cannot be used on columns with a timestamp data type or identity property.
    • DEFAULT constraints are automatically bound to a column when they are created.

    The general syntax for the DEFAULT constraint is:

    [CONSTRAINT constraint_name]
    DEFAULT {constant_expression | niladic-function | NULL}
    [FOR col_name]

    This example sets the default for the city field to ‘Vancouver’.

    USE HOTEL
    ALTER TABLE tblHotel
    Add CONSTRAINT df_city DEFAULT ‘Vancouver’ FOR City

    15.4: Table Constraints is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?