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 ¶

Trigger	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