Обработка баз данных на Visual Basic®.NET

Прайс Кевин Т.

 [RegionName] [varchar] (25) COLLATE Latin1_General_CI_AI NULL

) ON [PRIMARY]

GO

Далее следует создать ограничения, как показано в листинге 3.5.

Листинг 3.5. Сценарий создания ограничений для базы данных Novelty

ALTER TABLE [dbo].[tblCustomer] WITH NOCHECK ADD

 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED (

[ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblDepartment] WITH NOCHECK ADD

 CONSTRAINT [tblDepartment_IDPK] PRIMARY KEY CLUSTERED (

[ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo]. [tblEmployee] WITH NOCHECK ADD

 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED (

[ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblInventory] WITH NOCHECK ADD

 CONSTRAINT [PK_tblInventory] PRIMARY KEY CLUSTERED (

[ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblOrder] WITH NOCHECK ADD

 CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED (

[ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblOrderltem] WITH NOCHECK ADD

 CONSTRAINT [PK_tblOrderItem] PRIMARY KEY CLUSTERED (

[ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo]. [tblRegion] WITH NOCHECK ADD

 CONSTRAINT [PK_tblRegion] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

CREATE UNIQUE INDEX [IX_tblRegion] ON [dbo]. [tblRegion] ([State]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblCustomer] ADD

 CONSTRAINT [FK_tblCustomer_tblRegion] FOREIGN KEY ([State])

 references [dbo].[tblRegion] (

[State]

 ) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[tblEmployee] ADD

 CONSTRAINT [FK_tblEmployee_tblDepartment] FOREIGN KEY ([DepartmentID])

 REFERENCES [dbo].[tblDepartment] (

[ID]

 ) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo]. [tblOrder] ADD

 CONSTRAINT [FK_tblOrder_tblCustomer] FOREIGN KEY ( [CustomerID])

 REFERENCES [dbo].[tblCustomer] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[tblOrderItem] ADD

 CONSTRAINT [FK_tblOrderItem_tblInventory] FOREIGN KEY ([ItemID])

 REFERENCES [dbo].[tblInventory] ([ID])

 ON DELETE CASCADE ON UPDATE CASCADE,

 constraint [FK_tblOrderItem_tblOrder] foreign key ([OrderID])

 REFERENCES [dbo].[tblOrder] ([ID])

 ON DELETE CASCADE ON UPDATE CASCADE

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Наконец,

для создания представлений, хранимых процедур и триггеров следует выполнить сценарий из листинга 3.6.

Листинг 3.6. Сценарий создания представлений, хранимых процедур и триггеров

/****** object: View dbo.EmployeeDepartment_view Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE view EmployeeDepartment_view as

 select e.ID, FirstName, LastName, DepartmentName

 from tblEmployee e, tblDepartment t

 where e.DepartmentID = t.ID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:13 PM ******/

create view qryEmployee_view as

 SELECT ID, FirstName, LastName, DepartmentID from tblEmployee

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.DeleteEmployee Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE dbo.DeleteEmployee (@Original_ID int)

AS

 SET NOCOUNT OFF;

 DELETE FROM tblEmployee WHERE (ID = @Original_ID)