73 lines
2.2 KiB
Transact-SQL
73 lines
2.2 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
if exists (select 1 from sys.foreign_keys where name like 'FK_HolidayAllocation_Holiday')
|
|
begin
|
|
ALTER TABLE [dbo].[HolidayAllocation] DROP CONSTRAINT [FK_HolidayAllocation_Holiday]
|
|
end
|
|
GO
|
|
|
|
if exists (select 1 from sys.default_constraints where name like 'DF_HolidayAllocation_Id')
|
|
begin
|
|
ALTER TABLE [dbo].[HolidayAllocation] DROP CONSTRAINT [DF_HolidayAllocation_Id]
|
|
end
|
|
GO
|
|
|
|
if exists (select 1 from sys.indexes where name like 'IX_HolidayAllocation')
|
|
begin
|
|
DROP INDEX [IX_HolidayAllocation] ON [dbo].[HolidayAllocation]
|
|
end
|
|
GO
|
|
|
|
/****** Object: Table [dbo].[HolidayAllocation] Script Date: 2/16/2016 6:49:57 PM ******/
|
|
if exists (select 1 from sys.tables where name like 'HolidayAllocation')
|
|
begin
|
|
DROP TABLE [dbo].[HolidayAllocation]
|
|
end
|
|
GO
|
|
|
|
/****** Object: Table [dbo].[HolidayAllocation] Script Date: 2/16/2016 6:49:57 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[HolidayAllocation](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[HolidayId] [uniqueidentifier] NOT NULL,
|
|
[HolidayGroupId] [uniqueidentifier] NOT NULL,
|
|
[WeekEndingDate] [datetime] NOT NULL,
|
|
[Monday] [bit] NOT NULL,
|
|
[Tuesday] [bit] NOT NULL,
|
|
[Wednesday] [bit] NOT NULL,
|
|
[Thursday] [bit] NOT NULL,
|
|
[Friday] [bit] NOT NULL,
|
|
[Saturday] [bit] NOT NULL,
|
|
[Sunday] [bit] NOT NULL,
|
|
CONSTRAINT [PK_HolidayAllocation] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[HolidayAllocation] ADD CONSTRAINT [DF_HolidayAllocation_Id] DEFAULT (newid()) FOR [Id]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[HolidayAllocation] WITH CHECK ADD CONSTRAINT [FK_HolidayAllocation_Holiday] FOREIGN KEY([HolidayId])
|
|
REFERENCES [dbo].[Holiday] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[HolidayAllocation] CHECK CONSTRAINT [FK_HolidayAllocation_Holiday]
|
|
GO
|
|
|
|
CREATE UNIQUE NONCLUSTERED INDEX [IX_HolidayAllocation] ON [dbo].[HolidayAllocation]
|
|
(
|
|
[WeekEndingDate] ASC,
|
|
[HolidayGroupId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|