Прайс Кевин Т.
Шрифт:
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** object: Table [dbo].[tblOrder] Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE [dbo].[tblOrder] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[CustomerlD] [int] NULL,
[OrderDate] [datetime] NULL,
[Amount] [money] NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo].[tblOrderItem] Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE] (
[ID] [int] IDENTITY (1, 1) NOT NULL, [OrderID] [int] NOT NULL,
[int] NOT NULL, [Quantity] [int] NULL,
[Cost] [money] NULL
) ON [PRIMARY]
GO
/******* object: Table [dbo].[tblRegion] Script Date: 10-Jul -02 12:41:12 PM *******/
CREATE TABLE [dbo]. [tblRegion] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[State] [char] (2) COLLATE Latin1_General_CI_AI NOT NULL,
[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])