9.3: Optional Column Constraints

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
CREATE TABLE EMPLOYEES
(
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
CREATE TABLE DEPARTMENT
(
   DepartmentName Char(35)  NOT NULL,
   BudgetCode     Char(30)  NOT NULL,
   OfficeNumber   Char(15)  NOT NULL,
   Phone          Char(15)  NOT NULL,
   CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)
);

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

USE SW
CREATE TABLE PROJECT
(
   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,
   CONSTRAINT      ASSIGNMENT_PK  PRIMARY KEY(ProjectID)
);

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
CREATE TABLE ASSIGNMENT
(
   ProjectID       Int  NOT NULL,
   EmployeeNumber  Int  NOT NULL,
   HoursWorked     Numeric(6,2)  NULL,
);