23 lines
1.1 KiB
Transact-SQL
23 lines
1.1 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TeamAllocation]') AND type in (N'U'))
|
|
DROP TABLE [dbo].[TeamAllocation]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TeamAllocation] (
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[ScenarioId] [uniqueidentifier] NOT NULL,
|
|
[TeamId] [uniqueidentifier] NOT NULL,
|
|
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
|
|
[WeekEndingDate] [datetime] NOT NULL,
|
|
[Quantity] [decimal](18, 6) NOT NULL,
|
|
[LastUpdate] [datetime] NOT NULL,
|
|
CONSTRAINT [PK_TeamAllocation] PRIMARY KEY CLUSTERED ([Id]),
|
|
CONSTRAINT [IX_TeamAllocation] UNIQUE NONCLUSTERED ([ScenarioId], [ExpenditureCategoryId], [TeamId], [WeekEndingDate])
|
|
) ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_ExpenditureCategoryId] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
|
|
GO
|
|
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_ScenarioId] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
|
|
GO
|
|
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_TeamId] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
|
|
GO
|