HumanResources.Employee (table)

wikibot

Table	HumanResources.Employee
Description	Employee information such as salary, department, and title.

Columns

Column	Data Type	Nullable	Default	Description
EmployeeID	int	not null		Primary key for Employee records.
NationalIDNumber	nvarchar(15)	not null		Unique national identification number such as a social security number.
ContactID	int	not null		Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID	nvarchar(256)	not null		Network login.
ManagerID	int	null		Manager to whom the employee is assigned. Foreign Key to Employee.M
Title	nvarchar(50)	not null		Work title such as Buyer or Sales Representative.
BirthDate	datetime	not null		Date of birth.
MaritalStatus	nchar(1)	not null		M = Married, S = Single
Gender	nchar(1)	not null		M = Male, F = Female
HireDate	datetime	not null		Employee hired on this date.
SalariedFlag	dbo.Flag	not null	((1))	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours	smallint	not null	((0))	Number of available vacation hours.
SickLeaveHours	smallint	not null	((0))	Number of available sick leave hours.
CurrentFlag	dbo.Flag	not null	((1))	0 = Inactive, 1 = Active
rowguid	uniqueidentifier	not null	(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	not null	(getdate())	Date and time the record was last updated.

Primary Key

Primary Key	Columns
PK_Employee_EmployeeID	EmployeeID

Indexes

Index	Type	Columns
AK_Employee_LoginID	Unique	LoginID
AK_Employee_NationalIDNumber	Unique	NationalIDNumber
AK_Employee_rowguid	Unique	rowguid
IX_Employee_ManagerID		ManagerID

Check Constraints

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

Foreign Keys

Relation	Column	Referenced Column
Person.Contact	ContactID	ContactID
HumanResources.Employee	ManagerID	EmployeeID

Detail Tables

Detail Table	Column	Referencing Column
HumanResources.Employee	EmployeeID	ManagerID
HumanResources.EmployeeAddress	EmployeeID	EmployeeID
HumanResources.EmployeeDepartmentHistory	EmployeeID	EmployeeID
HumanResources.EmployeePayHistory	EmployeeID	EmployeeID
HumanResources.JobCandidate	EmployeeID	EmployeeID
Purchasing.PurchaseOrderHeader	EmployeeID	EmployeeID
Sales.SalesPerson	EmployeeID	SalesPersonID

Triggers

Triggers	Type
dEmployee	INSTEAD OF DELETE

Trigger dEmployee

CREATE 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.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;

References

Dependency Type	Object Type	Referenced Object
Data Type	Type	dbo.Flag
Schema	Schema	HumanResources

Dependencies

Reference Type	Object Type	Referencing Object
Select	View	HumanResources.vEmployee
Select	View	HumanResources.vEmployeeDepartment
Select	View	HumanResources.vEmployeeDepartmentHistory
Select	View	Sales.vSalesPerson
Select	View	Sales.vSalesPersonSalesByFiscalYears
Select	Procedure	dbo.uspGetEmployeeManagers
Select	Procedure	dbo.uspGetManagerEmployees
Update	Procedure	HumanResources.uspUpdateEmployeeHireInfo
Update	Procedure	HumanResources.uspUpdateEmployeeLogin
Update	Procedure	HumanResources.uspUpdateEmployeePersonalInfo
Select	SQL table-valued-function	dbo.ufnGetContactInformation

automatically generated

Table	HumanResources.Employee
Description	Employee information such as salary, department, and title.

Column	Data Type	Nullable	Default	Description / PK / Index
EmployeeID	int	not null		Primary key for Employee records. PK_Employee_EmployeeID
NationalIDNumber	nvarchar(15)	not null		Unique national identification number such as a social security number. AK_Employee_NationalIDNumber
ContactID	int	not null		Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID	nvarchar(256)	not null		Network login. AK_Employee_LoginID
ManagerID	int	null		Manager to whom the employee is assigned. Foreign Key to Employee.M IX_Employee_ManagerID
Title	nvarchar(50)	not null		Work title such as Buyer or Sales Representative.
BirthDate	datetime	not null		Date of birth.
MaritalStatus	nchar(1)	not null		M = Married, S = Single
Gender	nchar(1)	not null		M = Male, F = Female
HireDate	datetime	not null		Employee hired on this date.
SalariedFlag	Flag	not null	(1)	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours	smallint	not null	(0)	Number of available vacation hours.
SickLeaveHours	smallint	not null	(0)	Number of available sick leave hours.
CurrentFlag	Flag	not null	(1)	0 = Inactive, 1 = Active
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. AK_Employee_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Relation	Column	Referenced Column
Person.Contact	ContactID	ContactID
HumanResources.Employee	ManagerID	EmployeeID

Detail Table	Column	Referencing Column
HumanResources.Employee	EmployeeID	ManagerID
HumanResources.EmployeeAddress	EmployeeID	EmployeeID
HumanResources.EmployeeDepartmentHistory	EmployeeID	EmployeeID
HumanResources.EmployeePayHistory	EmployeeID	EmployeeID
HumanResources.JobCandidate	EmployeeID	EmployeeID
Purchasing.PurchaseOrderHeader	EmployeeID	EmployeeID
Sales.SalesPerson	EmployeeID	SalesPersonID

Triggers	Type
dEmployee	INSTEAD OF DELETE

Dependency Type	Object Type	Referenced Object
Data Type	Type	dbo.Flag
Schema	Schema	HumanResources

Reference Type	Object Type	Referencing Object
Select	View	HumanResources.vEmployee
Select	View	HumanResources.vEmployeeDepartment
Select	View	HumanResources.vEmployeeDepartmentHistory
Select	View	Sales.vSalesPerson
Select	View	Sales.vSalesPersonSalesByFiscalYears
Select	Procedure	dbo.uspGetEmployeeManagers
Select	Procedure	dbo.uspGetManagerEmployees
Update	Procedure	HumanResources.uspUpdateEmployeeHireInfo
Update	Procedure	HumanResources.uspUpdateEmployeeLogin
Update	Procedure	HumanResources.uspUpdateEmployeePersonalInfo
Select	SQL table-valued-function	dbo.ufnGetContactInformation