AdventureWorks
| Table Name: | HumanResources.Employee |
| Description: | Employee information such as salary, department, and title. |
| Primary Keys: | EmployeeID |
| Field | Type | Defaults | Nulls? | Comments |
|---|---|---|---|---|
| EmployeeID | int - Identity | None | No | Primary key for Employee records. |
| NationalIDNumber | nvarchar (15) | None | No | Unique national identification number such as a social security number. |
| ContactID | int | None | No | Identifies the employee in the Contact table. Foreign key to Contact.ContactID. |
| LoginID | nvarchar (256) | None | No | Network login. |
| ManagerID | int | None | Yes | Manager to whom the employee is assigned. Foreign Key to Employee.M |
| Title | nvarchar (50) | None | No | Work title such as Buyer or Sales Representative. |
| BirthDate | datetime | None | No | Date of birth. |
| MaritalStatus | nchar (1) | None | No | M = Married, S = Single |
| Gender | nchar (1) | None | No | M = Male, F = Female |
| HireDate | datetime | None | No | Employee hired on this date. |
| SalariedFlag | bit | ((1)) | No | Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining. |
| VacationHours | smallint | ((0)) | No | Number of available vacation hours. |
| SickLeaveHours | smallint | ((0)) | No | Number of available sick leave hours. |
| CurrentFlag | bit | ((1)) | No | 0 = Inactive, 1 = Active |
| rowguid | uniqueidentifier | (newid()) | No | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
| ModifiedDate | datetime | (getdate()) | No | Date and time the record was last updated. |
| Index | Clustered? | Unique? | Fields |
|---|---|---|---|
| PK_Employee_EmployeeID | Yes | Yes | EmployeeID |
| AK_Employee_LoginID | No | Yes | LoginID |
| AK_Employee_NationalIDNumber | No | Yes | NationalIDNumber |
| AK_Employee_rowguid | No | Yes | rowguid |
| IX_Employee_ManagerID | No | No | ManagerID |
| Check Constraint | Text |
|---|---|
| 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 Constraint | Affected Field | Source Table |
|---|---|---|
| FK_Employee_Contact_ContactID | ContactID | Person.Contact |
| FK_Employee_Employee_ManagerID | ManagerID | HumanResources.Employee |
| Primary Key as Foreign Key Constraint | Affected Table | Affected Field |
|---|---|---|
| FK_Employee_Employee_ManagerID | HumanResources.Employee | ManagerID |
| FK_EmployeeAddress_Employee_EmployeeID | HumanResources.EmployeeAddress | EmployeeID |
| FK_EmployeeDepartmentHistory_Employee_EmployeeID | HumanResources.EmployeeDepartmentHistory | EmployeeID |
| FK_EmployeePayHistory_Employee_EmployeeID | HumanResources.EmployeePayHistory | EmployeeID |
| FK_JobCandidate_Employee_EmployeeID | HumanResources.JobCandidate | EmployeeID |
| FK_PurchaseOrderHeader_Employee_EmployeeID | Purchasing.PurchaseOrderHeader | EmployeeID |
| FK_SalesPerson_Employee_SalesPersonID | Sales.SalesPerson | SalesPersonID |
| Trigger | Text |
|---|---|
| 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. IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END END; END; |