HumanResources.EmployeePayHistory Table

Description

Employee pay history.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	316
Data Space Used	16.00 KB
Index Space Used	16.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Employee identification number. Foreign key to Employee.BusinessEntityID.	Integer	4
RateChangeDate	Date the change in pay is effective	DBTimeStamp	4
Rate	Salary hourly rate.	Currency	8
PayFrequency	1 = Salary received monthly, 2 = Salary received biweekly	UnsignedTinyInt	1
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
PK_EmployeePayHistory_BusinessEntityID_RateChangeDate	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_EmployeePayHistory_PayFrequency	Check constraint [PayFrequency]=(3) OR [PayFrequency]=(2) OR [PayFrequency]=(1)	([PayFrequency]=(2) OR [PayFrequency]=(1))
CK_EmployeePayHistory_Rate	Check constraint [Rate] >= (6.50) AND [Rate] <= (200.00)	([Rate]>=(6.50) AND [Rate]<=(200.00))

Relationships

Relationship	Description
FK_EmployeePayHistory_Employee_BusinessEntityID	Foreign key constraint referencing Employee.EmployeeID.

Objects that depend on HumanResources.EmployeePayHistory

	Database Object	Object Type	Description	Dep Level
	HumanResources.uspUpdateEmployeeHireInfo	Stored Procedure	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.	1

Objects that HumanResources.EmployeePayHistory depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	3
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		3
dbo.NameStyle	User Defined Data Type		3
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeePayHistory](
    [BusinessEntityID] [int] NOT NULL,
    [RateChangeDate] [datetime] NOT NULL,
    [Rate] [money] NOT NULL,
    [PayFrequency] [tinyint] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeePayHistory_BusinessEntityID_RateChangeDate] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC,
    [RateChangeDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [HumanResources].[EmployeePayHistory] ADD  CONSTRAINT [DF_EmployeePayHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [HumanResources].[EmployeePayHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])
ALTER TABLE [HumanResources].[EmployeePayHistory] CHECK CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[EmployeePayHistory]  WITH CHECK ADD  CONSTRAINT [CK_EmployeePayHistory_PayFrequency] CHECK  (([PayFrequency]=(2) OR [PayFrequency]=(1)))
ALTER TABLE [HumanResources].[EmployeePayHistory] CHECK CONSTRAINT [CK_EmployeePayHistory_PayFrequency]
ALTER TABLE [HumanResources].[EmployeePayHistory]  WITH CHECK ADD  CONSTRAINT [CK_EmployeePayHistory_Rate] CHECK  (([Rate]>=(6.50) AND [Rate]<=(200.00)))
ALTER TABLE [HumanResources].[EmployeePayHistory] CHECK CONSTRAINT [CK_EmployeePayHistory_Rate]

Related Objects