HumanResources.EmployeeDepartmentHistory

Columns

• //

  ${escapify(column.description)}
  BusinessEntityID
  [int] NOT NULL
• //

  ${escapify(column.description)}
  DepartmentID
  [smallint] NOT NULL
• //

  ${escapify(column.description)}
  ShiftID
  [tinyint] NOT NULL
• //

  ${escapify(column.description)}
  StartDate
  [date] NOT NULL
• //

  ${escapify(column.description)}
  EndDate
  [date] NULL
• //

  ${escapify(column.description)}
  ModifiedDate
  [datetime] NOT NULL DEFAULT (getdate())

Indexes

• //

  Clustered index created by a primary key constraint.
  PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
  UNIQUE CLUSTERED
  BusinessEntityID , DepartmentID , ShiftID , StartDate
• //

  Nonclustered index.
  IX_EmployeeDepartmentHistory_DepartmentID
  NONCLUSTERED
  DepartmentID
• //

  Nonclustered index.
  IX_EmployeeDepartmentHistory_ShiftID
  NONCLUSTERED
  ShiftID

Foreign Keys

• //

  Foreign key constraint referencing Department.DepartmentID.
  FK_EmployeeDepartmentHistory_Department_DepartmentID
  HumanResources.Department
  DepartmentID ⥱ DepartmentID
• //

  Foreign key constraint referencing Employee.EmployeeID.
  FK_EmployeeDepartmentHistory_Employee_BusinessEntityID
  HumanResources.Employee
  BusinessEntityID ⥱ BusinessEntityID
• //

  Foreign key constraint referencing Shift.ShiftID
  FK_EmployeeDepartmentHistory_Shift_ShiftID
  HumanResources.Shift
  ShiftID ⥱ ShiftID

Check Constraints

• //

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

Sample Data