AdventureWorks

Basic Index   Expanded Index

Table Name:HumanResources.Employee
Description:Employee information such as salary, department, and title.
Primary Keys:EmployeeID
FieldTypeDefaultsNulls?Comments
EmployeeIDint - IdentityNoneNoPrimary key for Employee records.
NationalIDNumbernvarchar (15)NoneNoUnique national identification number such as a social security number.
ContactIDintNoneNoIdentifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginIDnvarchar (256)NoneNoNetwork login.
ManagerIDintNoneYesManager to whom the employee is assigned. Foreign Key to Employee.M
Titlenvarchar (50)NoneNoWork title such as Buyer or Sales Representative.
BirthDatedatetimeNoneNoDate of birth.
MaritalStatusnchar (1)NoneNoM = Married, S = Single
Gendernchar (1)NoneNoM = Male, F = Female
HireDatedatetimeNoneNoEmployee hired on this date.
SalariedFlagbit((1))NoJob classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHourssmallint((0))NoNumber of available vacation hours.
SickLeaveHourssmallint((0))NoNumber of available sick leave hours.
CurrentFlagbit((1))No0 = Inactive, 1 = Active
rowguiduniqueidentifier(newid())NoROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_Employee_EmployeeIDYesYesEmployeeID
AK_Employee_LoginIDNoYesLoginID
AK_Employee_NationalIDNumberNoYesNationalIDNumber
AK_Employee_rowguidNoYesrowguid
IX_Employee_ManagerIDNoNoManagerID

Check ConstraintText
CK_Employee_BirthDate([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
CK_Employee_MaritalStatus(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
CK_Employee_HireDate([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
CK_Employee_Gender(upper([Gender])='F' OR upper([Gender])='M')
CK_Employee_VacationHours([VacationHours]>=(-40) AND [VacationHours]<=(240))
CK_Employee_SickLeaveHours([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))

Internal Foreign Key ConstraintAffected FieldSource Table
FK_Employee_Contact_ContactIDContactIDPerson.Contact
FK_Employee_Employee_ManagerIDManagerIDHumanResources.Employee

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_Employee_Employee_ManagerIDHumanResources.EmployeeManagerID
FK_EmployeeAddress_Employee_EmployeeIDHumanResources.EmployeeAddressEmployeeID
FK_EmployeeDepartmentHistory_Employee_EmployeeIDHumanResources.EmployeeDepartmentHistoryEmployeeID
FK_EmployeePayHistory_Employee_EmployeeIDHumanResources.EmployeePayHistoryEmployeeID
FK_JobCandidate_Employee_EmployeeIDHumanResources.JobCandidateEmployeeID
FK_PurchaseOrderHeader_Employee_EmployeeIDPurchasing.PurchaseOrderHeaderEmployeeID
FK_SalesPerson_Employee_SalesPersonIDSales.SalesPersonSalesPersonID

TriggerText
dEmployeeCREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]
INSTEAD OF DELETE NOT FOR REPLICATION AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN
RAISERROR
(N'Employees cannot be deleted. They can only be marked as not current.', -- Message
10, -- Severity.
1); -- State.
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END;