· 8 years ago · Jun 03, 2018, 11:52 AM
1USE master;
2
3IF EXISTS(SELECT * FROM sys.databases where name = 'IndexesDB')
4 DROP DATABASE IndexesDB;
5GO
6CREATE DATABASE IndexesDB;
7GO
8USE IndexesDB;
9GO
10
11if exists (select 1
12 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
13 where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_REFERENCE_CUSTOMER')
14alter table "Order"
15 drop constraint FK_ORDER_REFERENCE_CUSTOMER
16go
17
18if exists (select 1
19 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
20 where r.fkeyid = object_id('OrderItem') and o.name = 'FK_ORDERITE_REFERENCE_ORDER')
21alter table OrderItem
22 drop constraint FK_ORDERITE_REFERENCE_ORDER
23go
24
25if exists (select 1
26 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
27 where r.fkeyid = object_id('OrderItem') and o.name = 'FK_ORDERITE_REFERENCE_PRODUCT')
28alter table OrderItem
29 drop constraint FK_ORDERITE_REFERENCE_PRODUCT
30go
31
32if exists (select 1
33 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
34 where r.fkeyid = object_id('Product') and o.name = 'FK_PRODUCT_REFERENCE_SUPPLIER')
35alter table Product
36 drop constraint FK_PRODUCT_REFERENCE_SUPPLIER
37go
38
39if exists (select 1
40 from sysindexes
41 where id = object_id('Customer')
42 and name = 'IndexCustomerName'
43 and indid > 0
44 and indid < 255)
45 drop index Customer.IndexCustomerName
46go
47
48if exists (select 1
49 from sysobjects
50 where id = object_id('Customer')
51 and type = 'U')
52 drop table Customer
53go
54
55if exists (select 1
56 from sysindexes
57 where id = object_id('"Order"')
58 and name = 'IndexOrderOrderDate'
59 and indid > 0
60 and indid < 255)
61 drop index "Order".IndexOrderOrderDate
62go
63
64if exists (select 1
65 from sysindexes
66 where id = object_id('"Order"')
67 and name = 'IndexOrderCustomerId'
68 and indid > 0
69 and indid < 255)
70 drop index "Order".IndexOrderCustomerId
71go
72
73if exists (select 1
74 from sysobjects
75 where id = object_id('"Order"')
76 and type = 'U')
77 drop table "Order"
78go
79
80if exists (select 1
81 from sysindexes
82 where id = object_id('OrderItem')
83 and name = 'IndexOrderItemProductId'
84 and indid > 0
85 and indid < 255)
86 drop index OrderItem.IndexOrderItemProductId
87go
88
89if exists (select 1
90 from sysindexes
91 where id = object_id('OrderItem')
92 and name = 'IndexOrderItemOrderId'
93 and indid > 0
94 and indid < 255)
95 drop index OrderItem.IndexOrderItemOrderId
96go
97
98if exists (select 1
99 from sysobjects
100 where id = object_id('OrderItem')
101 and type = 'U')
102 drop table OrderItem
103go
104
105if exists (select 1
106 from sysindexes
107 where id = object_id('Product')
108 and name = 'IndexProductName'
109 and indid > 0
110 and indid < 255)
111 drop index Product.IndexProductName
112go
113
114if exists (select 1
115 from sysindexes
116 where id = object_id('Product')
117 and name = 'IndexProductSupplierId'
118 and indid > 0
119 and indid < 255)
120 drop index Product.IndexProductSupplierId
121go
122
123if exists (select 1
124 from sysobjects
125 where id = object_id('Product')
126 and type = 'U')
127 drop table Product
128go
129
130if exists (select 1
131 from sysindexes
132 where id = object_id('Supplier')
133 and name = 'IndexSupplierCountry'
134 and indid > 0
135 and indid < 255)
136 drop index Supplier.IndexSupplierCountry
137go
138
139if exists (select 1
140 from sysindexes
141 where id = object_id('Supplier')
142 and name = 'IndexSupplierName'
143 and indid > 0
144 and indid < 255)
145 drop index Supplier.IndexSupplierName
146go
147
148if exists (select 1
149 from sysobjects
150 where id = object_id('Supplier')
151 and type = 'U')
152 drop table Supplier
153go
154
155/*==============================================================*/
156/* Table: Customer */
157/*==============================================================*/
158create table Customer (
159 Id int identity,
160 FirstName nvarchar(40) not null,
161 LastName nvarchar(40) not null,
162 City nvarchar(40) null,
163 Country nvarchar(40) null,
164 Phone nvarchar(20) null,
165 constraint PK_CUSTOMER primary key (Id)
166)
167go
168
169/*==============================================================*/
170/* Index: IndexCustomerName */
171/*==============================================================*/
172/*create index IndexCustomerName on Customer (
173LastName ASC,
174FirstName ASC
175)
176go*/
177
178/*==============================================================*/
179/* Table: "Order" */
180/*==============================================================*/
181create table "Order" (
182 Id int identity,
183 OrderDate datetime not null default getdate(),
184 OrderNumber nvarchar(10) null,
185 CustomerId int not null,
186 TotalAmount decimal(12,2) null default 0,
187 constraint PK_ORDER primary key (Id)
188)
189go
190
191/*==============================================================*/
192/* Index: IndexOrderCustomerId */
193/*==============================================================*/
194/*create index IndexOrderCustomerId on "Order" (
195CustomerId ASC
196)
197go*/
198
199/*==============================================================*/
200/* Index: IndexOrderOrderDate */
201/*==============================================================*/
202/*create index IndexOrderOrderDate on "Order" (
203OrderDate ASC
204)
205go*/
206
207/*==============================================================*/
208/* Table: OrderItem */
209/*==============================================================*/
210create table OrderItem (
211 Id int identity,
212 OrderId int not null,
213 ProductId int not null,
214 UnitPrice decimal(12,2) not null default 0,
215 Quantity int not null default 1,
216 constraint PK_ORDERITEM primary key (Id)
217)
218go
219
220/*==============================================================*/
221/* Index: IndexOrderItemOrderId */
222/*==============================================================*/
223/*create index IndexOrderItemOrderId on OrderItem (
224OrderId ASC
225)
226go*/
227
228/*==============================================================*/
229/* Index: IndexOrderItemProductId */
230/*==============================================================*/
231/*create index IndexOrderItemProductId on OrderItem (
232ProductId ASC
233)
234go*/
235
236/*==============================================================*/
237/* Table: Product */
238/*==============================================================*/
239create table Product (
240 Id int identity,
241 ProductName nvarchar(50) not null,
242 SupplierId int not null,
243 UnitPrice decimal(12,2) null default 0,
244 Package nvarchar(30) null,
245 IsDiscontinued bit not null default 0,
246 constraint PK_PRODUCT primary key (Id)
247)
248go
249
250/*==============================================================*/
251/* Index: IndexProductSupplierId */
252/*==============================================================*/
253/*
254*create index IndexProductSupplierId on Product (
255SupplierId ASC
256)
257go*/
258
259/*==============================================================*/
260/* Index: IndexProductName */
261/*==============================================================*/
262/*create index IndexProductName on Product (
263ProductName ASC
264)
265go*/
266
267/*==============================================================*/
268/* Table: Supplier */
269/*==============================================================*/
270create table Supplier (
271 Id int identity,
272 CompanyName nvarchar(40) not null,
273 ContactName nvarchar(50) null,
274 ContactTitle nvarchar(40) null,
275 City nvarchar(40) null,
276 Country nvarchar(40) null,
277 Phone nvarchar(30) null,
278 Fax nvarchar(30) null,
279 constraint PK_SUPPLIER primary key (Id)
280)
281go
282
283/*==============================================================*/
284/* Index: IndexSupplierName */
285/*==============================================================*/
286/*create index IndexSupplierName on Supplier (
287CompanyName ASC
288)
289go*/
290
291/*==============================================================*/
292/* Index: IndexSupplierCountry */
293/*==============================================================*/
294/*create index IndexSupplierCountry on Supplier (
295Country ASC
296)
297go*/
298
299alter table "Order"
300 add constraint FK_ORDER_REFERENCE_CUSTOMER foreign key (CustomerId)
301 references Customer (Id)
302go
303
304alter table OrderItem
305 add constraint FK_ORDERITE_REFERENCE_ORDER foreign key (OrderId)
306 references "Order" (Id)
307go
308
309alter table OrderItem
310 add constraint FK_ORDERITE_REFERENCE_PRODUCT foreign key (ProductId)
311 references Product (Id)
312go
313
314alter table Product
315 add constraint FK_PRODUCT_REFERENCE_SUPPLIER foreign key (SupplierId)
316 references Supplier (Id)
317go