Изменение таблиц базы данных

Листинг 6. Установка огр аничений и внешних ключей

USE [Tester]

ALTER TABLE [dbo].[Account] WITH CHECK ADD CONSTRAINT [FK_Account_Type] FOREIGN KEY([Account_Type])

REFERENCES [dbo].[Type] ([Id])

ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_Type]

ALTER TABLE [dbo].[Question] WITH CHECK ADD CONSTRAINT [UN_Question_Id_Test] FOREIGN KEY([Id_Test])

REFERENCES [dbo].[TestCase] ([Id])

ALTER TABLE [dbo].[Question] CHECK CONSTRAINT [UN_Question_Id_Test]

ALTER TABLE [dbo].[Session] WITH CHECK ADD CONSTRAINT [FK_Session_Account] FOREIGN KEY([Id_Account])

REFERENCES [dbo].[Account] ([Id])

ALTER TABLE [dbo].[Session] CHECK CONSTRAINT [FK_Session_Account]

ALTER TABLE [dbo].[Session] WITH CHECK ADD CONSTRAINT [FK_Session_TestCase] FOREIGN KEY([Id_Test])

REFERENCES [dbo].[TestCase] ([Id])

ALTER TABLE [dbo].[Session] CHECK CONSTRAINT [FK_Session_TestCase]

ALTER TABLE [dbo].[TestCase] WITH CHECK ADD CONSTRAINT [FK_TestCase_Id_Creator] FOREIGN KEY([Id_Creator])

REFERENCES [dbo].[Account] ([Id])

ALTER TABLE [dbo].[TestCase] CHECK CONSTRAINT [FK_TestCase_Id_Creator]

ALTER TABLE [dbo].[TestCase] WITH CHECK ADD CONSTRAINT [CK_TestCase_QuestionAmount] CHECK (([Questions_Amount]<(80) AND [Questions_Amount]>(0)))

ALTER TABLE [dbo].[TestCase] CHECK CONSTRAINT [CK_TestCase_QuestionAmount]

Листинг 7. Скрипт добавления нового столбца в таблицу Account

USE [Tester]

ALTER TABLE [dbo].[Account]

ADD passwd nvarchar(100) not null default '';

Рис 2. Дизайн отношения Account перед модификацией

Рис 3. Дизайн отношения Account после модификации