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