Skip to main content
Engineering LibreTexts

9.3: Optional Column Constraints

  • Page ID
  • \( \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}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    Optional Column Constraints

    The Optional ColumnConstraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that null values are allowed, which means that a row can be created without a value for this column. The column constraint NOT NULL indicates that a value must be supplied when a new row is created.

    To illustrate, we will use the SQL statement CREATE TABLE EMPLOYEES to create the employees table with 16 attributes or fields.

    USE SW
    EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,
    DepartmentName                  CHAR(30)             NOT NULL           DEFAULT “Human Resources”,
    FirstName                       CHAR(25)             NOT NULL,
    LastName                        CHAR(25)             NOT NULL,
    Category                        CHAR(20)             NOT NULL,
    HourlyRate                      CURRENCY             NOT NULL,
    TimeCard                        LOGICAL              NOT NULL,
    HourlySalaried                  CHAR(1)              NOT NULL,
    EmpType                         CHAR(1)              NOT NULL,
    Terminated                      LOGICAL              NOT NULL,
    ExemptCode                      CHAR(2)              NOT NULL,
    Supervisor                      LOGICAL              NOT NULL,
    SupervisorName                  CHAR(50)             NOT NULL,
    BirthDate                       DATE                 NOT NULL,
    CollegeDegree                   CHAR(5)              NOT NULL,
    CONSTRAINT                      Employee_PK          PRIMARY KEY(EmployeeNo

    The first field is EmployeeNo with a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL).

    Similarly, the second field is DepartmentName with a field type CHAR of length 30. After all the table columns are defined, a table constraint, identified by the word CONSTRAINT, is used to create the primary key:

    CONSTRAINT     EmployeePK      PRIMARY KEY(EmployeeNo)

    We will discuss the constraint property further later in this chapter.

    Likewise, we can create a Department  table,  a Project table and an Assignment table using the CREATE TABLE SQL DDL command as shown in the below example.

    USE SW
       DepartmentName Char(35)  NOT NULL,
       BudgetCode     Char(30)  NOT NULL,
       OfficeNumber   Char(15)  NOT NULL,
       Phone          Char(15)  NOT NULL,

    In this example, a project table is created with seven fields: ProjectID, ProjectName, Department, MaxHours, StartDate, and EndDate.

    USE SW
       ProjectID       Int  NOT NULL IDENTITY (1000,100),
       ProjectName     Char(50) NOT NULL,
       Department      Char(35) NOT NULL,
       MaxHours        Numeric(8,2)  NOT NULL DEFAULT 100,
       StartDate       DateTime NULL,
       EndDate         DateTime NULL,

    In this last example, an assignment table is created with three fields: ProjectID, EmployeeNumber, and HoursWorked. The assignment table is used to record who (EmployeeNumber) and how much time(HoursWorked) an employee worked on the particular project(ProjectID).

    USE SW
       ProjectID       Int  NOT NULL,
       EmployeeNumber  Int  NOT NULL,
       HoursWorked     Numeric(6,2)  NULL,

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

    • Was this article helpful?