--<< FileName:dbo_HRM_tblUserAndRelatedEmp.sql >>--
--<< TABLE DEFINITION >>--

IF OBJECT_ID('dbo.HRM_tblUserAndRelatedEmp') IS NULL
CREATE TABLE [dbo].[HRM_tblUserAndRelatedEmp](
	[UserAndRelatedEmpId] [int] IDENTITY(1,1) NOT NULL,
	[ApplyYearMonth] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[UserPartyID] [int] NOT NULL,
	[RelatedEmpID] [int] NOT NULL,
	[TimeTag] [timestamp] NOT NULL
) ON [PRIMARY]

GO

--<< INDEXES DEFINITION >>--
IF NOT EXISTS(SELECT * FROM SYS.INDEXES WHERE NAME = 'IX_HRM_tblUserAndRelatedEmp_ApplyYearMonth')
CREATE NONCLUSTERED INDEX IX_HRM_tblUserAndRelatedEmp_ApplyYearMonth ON [dbo].[HRM_tblUserAndRelatedEmp]
(
	ApplyYearMonth
)  ON [SG_Index]

GO

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE name = 'IX_HRM_tblUserAndRelatedEmp_UserPartyID' 
AND OBJECT_ID('dbo.HRM_tblUserAndRelatedEmp') = object_id)
CREATE NONCLUSTERED INDEX IX_HRM_tblUserAndRelatedEmp_UserPartyID
ON [dbo].[HRM_tblUserAndRelatedEmp] 
(
	UserPartyID
 ) 
ON [SG_Index]
GO

--<< FOREIGNKEYS DEFINITION >>--

IF NOT EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = 'FK_HRM_tblUserAndRelatedEmp_CMS_tblMonthRange')
ALTER TABLE [dbo].[HRM_tblUserAndRelatedEmp]  ADD  CONSTRAINT [FK_HRM_tblUserAndRelatedEmp_CMS_tblMonthRange] 
FOREIGN KEY([ApplyYearMonth])
REFERENCES [dbo].[CMS_tblMonthRange] ([Code])

GO

IF NOT EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = 'FK_HRM_tblUserAndRelatedEmp_UserPartyID_HRM_tblEmployee')
ALTER TABLE [dbo].[HRM_tblUserAndRelatedEmp]  ADD  CONSTRAINT [FK_HRM_tblUserAndRelatedEmp_UserPartyID_HRM_tblEmployee]
 FOREIGN KEY([UserPartyID])
REFERENCES [dbo].[CMN_tblParty] ([PartyID])

GO

IF NOT EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = 'FK_HRM_tblUserAndRelatedEmp_UserID_HRM_tblUser')
ALTER TABLE [dbo].[HRM_tblUserAndRelatedEmp]  ADD  CONSTRAINT [FK_HRM_tblUserAndRelatedEmp_UserID_HRM_tblUser]
 FOREIGN KEY([UserID])
REFERENCES [dbo].[SMS_tblUser] ([UserID])

GO

IF NOT EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = 'FK_HRM_tblUserAndRelatedEmp_RelatedEmpID_HRM_tblEmployee')
ALTER TABLE [dbo].[HRM_tblUserAndRelatedEmp]  ADD  CONSTRAINT [FK_HRM_tblUserAndRelatedEmp_RelatedEmpID_HRM_tblEmployee]
 FOREIGN KEY([RelatedEmpID])
REFERENCES [dbo].[HRM_tblEmployee] ([EmployeeID])

GO



