Welcome Guest, you are in: Login

devio dbscript ScrewTurn Wiki

RSS RSS

Navigation





Search the wiki
»

PoweredBy

HumanResources Employee (table)

RSS
Modified on 2011/08/27 11:17 by wikibot Categorized as Uncategorized

wikibot

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

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.

Primary Key

Primary KeyColumns
PK_Employee_EmployeeIDEmployeeID

Indexes

IndexTypeColumns
AK_Employee_LoginIDUniqueLoginID
AK_Employee_NationalIDNumberUniqueNationalIDNumber
AK_Employee_rowguidUniquerowguid
IX_Employee_ManagerID ManagerID

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)

Foreign Keys

RelationColumnReferenced Column
Person.ContactContactIDContactID
HumanResources.EmployeeManagerIDEmployeeID

Detail Tables

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

Triggers

TriggerType
dEmployeeINSTEAD 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 TypeObject TypeReferenced Object
Data TypeTypedbo.Flag
SchemaSchemaHumanResources

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

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