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,
);