70 lines
2.1 KiB
Transact-SQL
70 lines
2.1 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
|
|
IF EXISTS (SELECT * FROM sys.tables where name='Skill2Resource')
|
|
begin
|
|
DROP TABLE [dbo].[Skill2Resource]
|
|
end
|
|
|
|
IF EXISTS (SELECT * FROM sys.tables where name='Skill')
|
|
begin
|
|
DROP TABLE [dbo].[Skill]
|
|
end
|
|
|
|
go
|
|
|
|
CREATE TABLE [dbo].[Skill](
|
|
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Skill_Id] DEFAULT (newid()),
|
|
[DateCreated] [datetime] NOT NULL,
|
|
[Name] [nvarchar](512) NOT NULL,
|
|
[DateEdited] [timestamp] NOT NULL,
|
|
[ParentId] [uniqueidentifier] NULL,
|
|
[HasChildren] [bit] NOT NULL,
|
|
[OrderNumber] [int] NULL
|
|
CONSTRAINT [PK_Skill] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Skill] WITH CHECK ADD CONSTRAINT [FK_Skill_Skill] FOREIGN KEY([ParentId])
|
|
REFERENCES [dbo].[Skill] ([Id])
|
|
GO
|
|
ALTER TABLE [dbo].[Skill] CHECK CONSTRAINT [FK_Skill_Skill]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[Skill2Resource](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[SkillId] [uniqueidentifier] NOT NULL,
|
|
[ResourceId] [uniqueidentifier] NOT NULL,
|
|
[Level] [smallint] NOT NULL,
|
|
[DateCreated] [datetime] not null
|
|
CONSTRAINT [PK_Skill2Resource] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
|
|
CONSTRAINT [UK_Skill2Resource] UNIQUE NONCLUSTERED
|
|
(
|
|
[ResourceId] ASC,
|
|
[SkillId] 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].[Skill2Resource] WITH CHECK ADD CONSTRAINT [FK_Skill2Resource_ResourceId] FOREIGN KEY([ResourceId])
|
|
REFERENCES [dbo].[PeopleResource] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Skill2Resource] CHECK CONSTRAINT [FK_Skill2Resource_ResourceId]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Skill2Resource] WITH CHECK ADD CONSTRAINT [FK_Skill2Resource_SkillId] FOREIGN KEY([SkillId])
|
|
REFERENCES [dbo].[Skill] ([Id])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[Skill2Resource] CHECK CONSTRAINT [FK_Skill2Resource_SkillId]
|
|
GO |