ScrewTurn Wiki

Edit

wikibot

TableHumanResources.Employee
DescriptionEmployee information such as salary, department, and title.

Edit

Columns

ColumnData TypeNullableDefaultDescription
EmployeeIDintnot null Primary key for Employee records.
NationalIDNumbernvarchar(15)not null Unique national identification number such as a social security number.
ContactIDintnot null Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginIDnvarchar(256)not null Network login.
ManagerIDintnull Manager to whom the employee is assigned. Foreign Key to Employee.M
Titlenvarchar(50)not null Work title such as Buyer or Sales Representative.
BirthDatedatetimenot null Date of birth.
MaritalStatusnchar(1)not null M = Married, S = Single
Gendernchar(1)not null M = Male, F = Female
HireDatedatetimenot null Employee hired on this date.
SalariedFlagdbo.Flagnot null((1))Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHourssmallintnot null((0))Number of available vacation hours.
SickLeaveHourssmallintnot null((0))Number of available sick leave hours.
CurrentFlagdbo.Flagnot null((1))0 = Inactive, 1 = Active
rowguiduniqueidentifiernot null(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Edit

Primary Key

Primary KeyColumns
PK_Employee_EmployeeIDEmployeeID

Edit

Indexes

IndexTypeColumns
AK_Employee_LoginIDUniqueLoginID
AK_Employee_NationalIDNumberUniqueNationalIDNumber
AK_Employee_rowguidUniquerowguid
IX_Employee_ManagerID ManagerID

Edit

Check Constraints

Check ConstraintExpressionDescription
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)

Edit

Foreign Keys

RelationColumnReferenced Column
Person.ContactContactIDContactID
HumanResources.EmployeeManagerIDEmployeeID

Edit

Detail Tables

Detail TableColumnReferencing Column
HumanResources.EmployeeEmployeeIDManagerID
HumanResources.EmployeeAddressEmployeeIDEmployeeID
HumanResources.EmployeeDepartmentHistoryEmployeeIDEmployeeID
HumanResources.EmployeePayHistoryEmployeeIDEmployeeID
HumanResources.JobCandidateEmployeeIDEmployeeID
Purchasing.PurchaseOrderHeaderEmployeeIDEmployeeID
Sales.SalesPersonEmployeeIDSalesPersonID

Edit

Triggers

TriggerType
dEmployeeINSTEAD OF DELETE

Edit

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;

Edit

References

Dependency TypeObject TypeReferenced Object
Data TypeTypedbo.Flag
SchemaSchemaHumanResources

Edit

Dependencies

Reference TypeObject TypeReferencing Object
SelectViewHumanResources.vEmployee
SelectViewHumanResources.vEmployeeDepartment
SelectViewHumanResources.vEmployeeDepartmentHistory
SelectViewSales.vSalesPerson
SelectViewSales.vSalesPersonSalesByFiscalYears
SelectProceduredbo.uspGetEmployeeManagers
SelectProceduredbo.uspGetManagerEmployees
UpdateProcedureHumanResources.uspUpdateEmployeeHireInfo
UpdateProcedureHumanResources.uspUpdateEmployeeLogin
UpdateProcedureHumanResources.uspUpdateEmployeePersonalInfo
SelectSQL table-valued-functiondbo.ufnGetContactInformation

Edit

automatically generated

TableHumanResources.Employee
DescriptionEmployee information such as salary, department, and title.

ColumnData TypeNullableDefaultDescription / PK / Index
EmployeeIDintnot null Primary key for Employee records.
PK_Employee_EmployeeID
NationalIDNumbernvarchar(15)not null Unique national identification number such as a social security number.
AK_Employee_NationalIDNumber
ContactIDintnot null Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginIDnvarchar(256)not null Network login.
AK_Employee_LoginID
ManagerIDintnull Manager to whom the employee is assigned. Foreign Key to Employee.M
IX_Employee_ManagerID
Titlenvarchar(50)not null Work title such as Buyer or Sales Representative.
BirthDatedatetimenot null Date of birth.
MaritalStatusnchar(1)not null M = Married, S = Single
Gendernchar(1)not null M = Male, F = Female
HireDatedatetimenot null Employee hired on this date.
SalariedFlagFlagnot null(1)Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHourssmallintnot null(0)Number of available vacation hours.
SickLeaveHourssmallintnot null(0)Number of available sick leave hours.
CurrentFlagFlagnot null(1)0 = Inactive, 1 = Active
rowguiduniqueidentifiernot null(NEWID())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
AK_Employee_rowguid
ModifiedDatedatetimenot null(GETDATE())Date and time the record was last updated.



RelationColumnReferenced Column
Person.ContactContactIDContactID
HumanResources.EmployeeManagerIDEmployeeID

Detail TableColumnReferencing Column
HumanResources.EmployeeEmployeeIDManagerID
HumanResources.EmployeeAddressEmployeeIDEmployeeID
HumanResources.EmployeeDepartmentHistoryEmployeeIDEmployeeID
HumanResources.EmployeePayHistoryEmployeeIDEmployeeID
HumanResources.JobCandidateEmployeeIDEmployeeID
Purchasing.PurchaseOrderHeaderEmployeeIDEmployeeID
Sales.SalesPersonEmployeeIDSalesPersonID

TriggersType
dEmployeeINSTEAD OF DELETE

Dependency TypeObject TypeReferenced Object
Data TypeTypedbo.Flag
SchemaSchemaHumanResources

Reference TypeObject TypeReferencing Object
SelectViewHumanResources.vEmployee
SelectViewHumanResources.vEmployeeDepartment
SelectViewHumanResources.vEmployeeDepartmentHistory
SelectViewSales.vSalesPerson
SelectViewSales.vSalesPersonSalesByFiscalYears
SelectProceduredbo.uspGetEmployeeManagers
SelectProceduredbo.uspGetManagerEmployees
UpdateProcedureHumanResources.uspUpdateEmployeeHireInfo
UpdateProcedureHumanResources.uspUpdateEmployeeLogin
UpdateProcedureHumanResources.uspUpdateEmployeePersonalInfo
SelectSQL table-valued-functiondbo.ufnGetContactInformation

ScrewTurn Wiki version 2.0.36. Some of the icons created by FamFamFam.