Data integrity is important in any environment. One way to enforce integrity is by using table constraints. You can add constraints when creating tables or alter the tables later. Listed below are the list of the constraints and why they are used in your tables.

• PRIMARY KEY: Ensures a column has a unique value for each record
• NOT NULL: Enforces that every record has a value for the column
• DEFAULT: The value of a column if a value has not been specified
• UNIQUE: Ensures that each column uses a distinct value
• CHECK: Enforce specific rules that a column must follow
• FOREIGN KEY: Enforces the child relationship with a parent table

Next, we will demonstrate how to use these constraints in our code when creating the HR.Employees table. In the following code example on Line 2 we see a demonstration of using a PRIMARY KEY constraint for the EmpID field. This will be the value that uniquely identifies each of the records in the table. This will also ensure that a NULL value cannot be placed in that field. Another way to ensure that NULL values cannot be used in a field is to use the NOT NULL constraint as seen on line 3, 4, and 5.

CREATE TABLE HR.Employees
(EmpID int IDENTITY Primary Key,
 FirstName varchar(15) NOT NULL,
 LastName varchar(20) NOT NULL,
 JobTitle varchar(10) NOT NULL,
 HireDate date DEFAULT GETDATE(),
 BirthDate date NULL,
 PhoneNumber varchar(15) UNIQUE,
 DeptCode tinyint)

You may also notice that on line 7 we are explicitly allowing NULL values for the BirthDate field. If NULL or NOT NULL is not specified on a column then those fields will also allow NULL values. (Such as line 6, 8, and 9)

Another way of ensuring a value is placed into a field is to use a DEFAULT constraint such as the one used for the HireDate field on line 6. In this case the DEFAULT constraint will put the current date into the field if a date was not supplied when the record was inserted.

On fields that would not be useful as a PRIMARY KEY but will still require distinct values you can use the UNIQUE constraint to ensure that duplicate values will not be used in a column. In the example above we are using a UNIQUE constraint on the PhoneNumber field to ensure that all employees have a unique phone number. While a PRIMARY KEY will not allow any NULL values, the UNIQUE constraint will allow one record to have a NULL value.

If you want to add a constraint after a table has already been created, you can do so by altering the table and adding the constraint. Be sure to give the constraint a descriptive name.

In the following example we will add a CHECK constraint to ensure that any date that is added to the HireDate column will need to be 18 years greater than the BirthDate field.

 ALTER TABLE HR.Employees
 ADD CONSTRAINT CK_HireDate18
 CHECK (HireDate > DATEADD(YY, 18, BirthDate))

Finally, before we can add a FOREIGN KEY constraint, we will first need to create a parent table named HR.Departmens for the DeptCode field. We will keep it simple with just two columns DeptID and DeptName. We will also INSERT four rows into the HR.Departments table. Then we will alter the Employees table to create the relationship that will enforce referential integrity between the two tables.

--Create table to join to HR.Employees
CREATE TABLE HR.Departments
(DeptID tinyint PRIMARY KEY,
 DeptName varchar(15))

 --Insert values into new table
 INSERT INTO HR.Departments
 VALUES (1, 'Accounting'), (2, 'Training'),
	    (3, 'Sales'), (4, 'Marketing')

--Add Foreign Key Constraint
ALTER TABLE HR.Employees
ADD CONSTRAINT FK_Emp_Dept FOREIGN KEY (DeptCode)
REFERENCES HR.Departments (DeptID)
ON UPDATE CASCADE ON DELETE SET NULL

You may have noticed that we are taking the DeptCode field from the HR.Employees table and connecting it to the DeptID field in the HR.Departments table. While the names for the fields can be different you will need to ensure that the data types and fields sizes are the same for both columns.

The final line of the Foreign Key constraint is what action to take if a value was updated or deleted in the parent table. In this case, if someone updated a value of the DeptID field in the HR.Departments table it would CASCADE down and update the same values in the child table, which would be the DeptCode field in the HR.Employees table.

If someone deleted a value in the DeptID field in the HR.Departments table then the DeptCode field in the HR.Employees table that previously matched that value would be set to NULL. If this line is not added to the Foreign Key constraint, updated and deletes would not be allowed on the DeptID field of the HR.Departments tables.