AdventureWorks

Basic Index   Expanded Index

Table Name:HumanResources.EmployeeDepartmentHistory
Description:Employee department transfers.
Primary Keys:EmployeeID
StartDate
DepartmentID
ShiftID
FieldTypeDefaultsNulls?Comments
EmployeeIDintNoneNoEmployee identification number. Foreign key to Employee.EmployeeID.
DepartmentIDsmallintNoneNoDepartment in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftIDtinyintNoneNoIdentifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDatedatetimeNoneNoDate the employee started work in the department.
EndDatedatetimeNoneYesDate the employee left the department. NULL = Current department.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDYesYesEmployeeID
StartDate
DepartmentID
ShiftID
IX_EmployeeDepartmentHistory_DepartmentIDNoNoDepartmentID
IX_EmployeeDepartmentHistory_ShiftIDNoNoShiftID

Check ConstraintText
CK_EmployeeDepartmentHistory_EndDate([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Internal Foreign Key ConstraintAffected FieldSource Table
FK_EmployeeDepartmentHistory_Department_DepartmentIDDepartmentIDHumanResources.Department
FK_EmployeeDepartmentHistory_Employee_EmployeeIDEmployeeIDHumanResources.Employee
FK_EmployeeDepartmentHistory_Shift_ShiftIDShiftIDHumanResources.Shift

No primary keys as foreign key constraints
No triggers