· 8 years ago · Nov 27, 2017, 01:36 PM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2008 */
3/* Created on: 27.11.2017 14:27:01 */
4/*==============================================================*/
5
6
7if exists (select 1
8 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
9 where r.fkeyid = object_id('Faktura') and o.name = 'FK_FAKTURA_R_1_FORMA_PL')
10alter table Faktura
11 drop constraint FK_FAKTURA_R_1_FORMA_PL
12go
13
14if exists (select 1
15 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
16 where r.fkeyid = object_id('Pracownik') and o.name = 'FK_PRACOWNI_R_5_FILIA')
17alter table Pracownik
18 drop constraint FK_PRACOWNI_R_5_FILIA
19go
20
21if exists (select 1
22 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
23 where r.fkeyid = object_id('Pracownik') and o.name = 'FK_PRACOWNI_R_6_STANOWIS')
24alter table Pracownik
25 drop constraint FK_PRACOWNI_R_6_STANOWIS
26go
27
28if exists (select 1
29 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
30 where r.fkeyid = object_id('Pracownik') and o.name = 'FK_PRACOWNI_R_7_DZIAL')
31alter table Pracownik
32 drop constraint FK_PRACOWNI_R_7_DZIAL
33go
34
35if exists (select 1
36 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
37 where r.fkeyid = object_id('R_3') and o.name = 'FK_R_3_R_3_ZAMOWIEN')
38alter table R_3
39 drop constraint FK_R_3_R_3_ZAMOWIEN
40go
41
42if exists (select 1
43 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
44 where r.fkeyid = object_id('R_3') and o.name = 'FK_R_3_R_4_PRODUKT')
45alter table R_3
46 drop constraint FK_R_3_R_4_PRODUKT
47go
48
49if exists (select 1
50 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
51 where r.fkeyid = object_id('Zamowienie') and o.name = 'FK_ZAMOWIEN_R_2_FAKTURA')
52alter table Zamowienie
53 drop constraint FK_ZAMOWIEN_R_2_FAKTURA
54go
55
56if exists (select 1
57 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
58 where r.fkeyid = object_id('Zamowienie') and o.name = 'FK_ZAMOWIEN_R_8_KLIENT')
59alter table Zamowienie
60 drop constraint FK_ZAMOWIEN_R_8_KLIENT
61go
62
63if exists (select 1
64 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
65 where r.fkeyid = object_id('Zamowienie') and o.name = 'FK_ZAMOWIEN_R_9_PRACOWNI')
66alter table Zamowienie
67 drop constraint FK_ZAMOWIEN_R_9_PRACOWNI
68go
69
70if exists (select 1
71 from sysobjects
72 where id = object_id('Dzial')
73 and type = 'U')
74 drop table Dzial
75go
76
77if exists (select 1
78 from sysindexes
79 where id = object_id('Faktura')
80 and name = 'R_1_FK'
81 and indid > 0
82 and indid < 255)
83 drop index Faktura.R_1_FK
84go
85
86if exists (select 1
87 from sysobjects
88 where id = object_id('Faktura')
89 and type = 'U')
90 drop table Faktura
91go
92
93if exists (select 1
94 from sysobjects
95 where id = object_id('Filia')
96 and type = 'U')
97 drop table Filia
98go
99
100if exists (select 1
101 from sysobjects
102 where id = object_id('Forma_platnosci')
103 and type = 'U')
104 drop table Forma_platnosci
105go
106
107if exists (select 1
108 from sysobjects
109 where id = object_id('Klient')
110 and type = 'U')
111 drop table Klient
112go
113
114if exists (select 1
115 from sysindexes
116 where id = object_id('Pracownik')
117 and name = 'R_7_FK'
118 and indid > 0
119 and indid < 255)
120 drop index Pracownik.R_7_FK
121go
122
123if exists (select 1
124 from sysindexes
125 where id = object_id('Pracownik')
126 and name = 'R_6_FK'
127 and indid > 0
128 and indid < 255)
129 drop index Pracownik.R_6_FK
130go
131
132if exists (select 1
133 from sysindexes
134 where id = object_id('Pracownik')
135 and name = 'R_5_FK'
136 and indid > 0
137 and indid < 255)
138 drop index Pracownik.R_5_FK
139go
140
141if exists (select 1
142 from sysobjects
143 where id = object_id('Pracownik')
144 and type = 'U')
145 drop table Pracownik
146go
147
148if exists (select 1
149 from sysobjects
150 where id = object_id('Produkt')
151 and type = 'U')
152 drop table Produkt
153go
154
155if exists (select 1
156 from sysindexes
157 where id = object_id('R_3')
158 and name = 'R_4_FK'
159 and indid > 0
160 and indid < 255)
161 drop index R_3.R_4_FK
162go
163
164if exists (select 1
165 from sysindexes
166 where id = object_id('R_3')
167 and name = 'R_3_FK'
168 and indid > 0
169 and indid < 255)
170 drop index R_3.R_3_FK
171go
172
173if exists (select 1
174 from sysobjects
175 where id = object_id('R_3')
176 and type = 'U')
177 drop table R_3
178go
179
180if exists (select 1
181 from sysobjects
182 where id = object_id('Stanowisko')
183 and type = 'U')
184 drop table Stanowisko
185go
186
187if exists (select 1
188 from sysindexes
189 where id = object_id('Zamowienie')
190 and name = 'R_9_FK'
191 and indid > 0
192 and indid < 255)
193 drop index Zamowienie.R_9_FK
194go
195
196if exists (select 1
197 from sysindexes
198 where id = object_id('Zamowienie')
199 and name = 'R_8_FK'
200 and indid > 0
201 and indid < 255)
202 drop index Zamowienie.R_8_FK
203go
204
205if exists (select 1
206 from sysindexes
207 where id = object_id('Zamowienie')
208 and name = 'R_2_FK'
209 and indid > 0
210 and indid < 255)
211 drop index Zamowienie.R_2_FK
212go
213
214if exists (select 1
215 from sysobjects
216 where id = object_id('Zamowienie')
217 and type = 'U')
218 drop table Zamowienie
219go
220
221/*==============================================================*/
222/* Table: Dzial */
223/*==============================================================*/
224create table Dzial (
225 Id_dzial int not null,
226 Nazwa_dzial varchar(40) not null,
227 constraint PK_DZIAL primary key nonclustered (Id_dzial)
228)
229go
230
231/*==============================================================*/
232/* Table: Faktura */
233/*==============================================================*/
234create table Faktura (
235 Id_faktura int not null,
236 Id_forma_platnosci int not null,
237 Nr_faktura varchar(20) not null,
238 Data_wystawienia_faktura datetime not null,
239 Termin_platnosci_faktura datetime null,
240 constraint PK_FAKTURA primary key nonclustered (Id_faktura)
241)
242go
243
244/*==============================================================*/
245/* Index: R_1_FK */
246/*==============================================================*/
247create index R_1_FK on Faktura (
248Id_forma_platnosci ASC
249)
250go
251
252/*==============================================================*/
253/* Table: Filia */
254/*==============================================================*/
255create table Filia (
256 Id_filia int not null,
257 Nazwa_filia varchar(30) not null,
258 constraint PK_FILIA primary key nonclustered (Id_filia)
259)
260go
261
262/*==============================================================*/
263/* Table: Forma_platnosci */
264/*==============================================================*/
265create table Forma_platnosci (
266 Id_forma_platnosci int not null,
267 Nazwa_forma_platnosci varchar(20) not null,
268 constraint PK_FORMA_PLATNOSCI primary key nonclustered (Id_forma_platnosci)
269)
270go
271
272/*==============================================================*/
273/* Table: Klient */
274/*==============================================================*/
275create table Klient (
276 Id_klient int not null,
277 Imie_klient varchar(20) not null,
278 Nazwisko_klient varchar(20) not null,
279 Adres_1_klient varchar(50) not null,
280 Adres_2_klient char(6) not null,
281 Adres_3_klient varchar(30) not null,
282 constraint PK_KLIENT primary key nonclustered (Id_klient)
283)
284go
285
286/*==============================================================*/
287/* Table: Pracownik */
288/*==============================================================*/
289create table Pracownik (
290 Id_pracownik int not null,
291 Id_dzial int not null,
292 Id_stanowisko int not null,
293 Id_filia int not null,
294 Imie_pracownik varchar(30) not null,
295 Nazwisko_pracownik varchar(30) not null,
296 Pesel_pracownik char(11) not null,
297 constraint PK_PRACOWNIK primary key nonclustered (Id_pracownik)
298)
299go
300
301/*==============================================================*/
302/* Index: R_5_FK */
303/*==============================================================*/
304create index R_5_FK on Pracownik (
305Id_filia ASC
306)
307go
308
309/*==============================================================*/
310/* Index: R_6_FK */
311/*==============================================================*/
312create index R_6_FK on Pracownik (
313Id_stanowisko ASC
314)
315go
316
317/*==============================================================*/
318/* Index: R_7_FK */
319/*==============================================================*/
320create index R_7_FK on Pracownik (
321Id_dzial ASC
322)
323go
324
325/*==============================================================*/
326/* Table: Produkt */
327/*==============================================================*/
328create table Produkt (
329 Id_produkt int not null,
330 Nazwa_produkt varchar(30) not null,
331 Cena_produkt decimal(8,2) not null,
332 constraint PK_PRODUKT primary key nonclustered (Id_produkt)
333)
334go
335
336/*==============================================================*/
337/* Table: R_3 */
338/*==============================================================*/
339create table R_3 (
340 Id_zamowienie int not null,
341 Id_produkt int not null,
342 Ilosc_produkt int not null,
343 constraint PK_R_3 primary key nonclustered (Id_zamowienie, Id_produkt)
344)
345go
346
347/*==============================================================*/
348/* Index: R_3_FK */
349/*==============================================================*/
350create index R_3_FK on R_3 (
351Id_zamowienie ASC
352)
353go
354
355/*==============================================================*/
356/* Index: R_4_FK */
357/*==============================================================*/
358create index R_4_FK on R_3 (
359Id_produkt ASC
360)
361go
362
363/*==============================================================*/
364/* Table: Stanowisko */
365/*==============================================================*/
366create table Stanowisko (
367 Id_stanowisko int not null,
368 Nazwa_stanowiska varchar(40) not null,
369 constraint PK_STANOWISKO primary key nonclustered (Id_stanowisko)
370)
371go
372
373/*==============================================================*/
374/* Table: Zamowienie */
375/*==============================================================*/
376create table Zamowienie (
377 Id_zamowienie int not null,
378 Id_klient int not null,
379 Id_pracownik int not null,
380 Id_faktura int not null,
381 Numer_zamowienie int not null,
382 Data_przyjecia_zamowienie datetime not null,
383 Data_realizacji_zamowienie datetime not null,
384 constraint PK_ZAMOWIENIE primary key nonclustered (Id_zamowienie)
385)
386go
387
388/*==============================================================*/
389/* Index: R_2_FK */
390/*==============================================================*/
391create index R_2_FK on Zamowienie (
392Id_faktura ASC
393)
394go
395
396/*==============================================================*/
397/* Index: R_8_FK */
398/*==============================================================*/
399create index R_8_FK on Zamowienie (
400Id_klient ASC
401)
402go
403
404/*==============================================================*/
405/* Index: R_9_FK */
406/*==============================================================*/
407create index R_9_FK on Zamowienie (
408Id_pracownik ASC
409)
410go
411
412alter table Faktura
413 add constraint FK_FAKTURA_R_1_FORMA_PL foreign key (Id_forma_platnosci)
414 references Forma_platnosci (Id_forma_platnosci)
415go
416
417alter table Pracownik
418 add constraint FK_PRACOWNI_R_5_FILIA foreign key (Id_filia)
419 references Filia (Id_filia)
420go
421
422alter table Pracownik
423 add constraint FK_PRACOWNI_R_6_STANOWIS foreign key (Id_stanowisko)
424 references Stanowisko (Id_stanowisko)
425go
426
427alter table Pracownik
428 add constraint FK_PRACOWNI_R_7_DZIAL foreign key (Id_dzial)
429 references Dzial (Id_dzial)
430go
431
432alter table R_3
433 add constraint FK_R_3_R_3_ZAMOWIEN foreign key (Id_zamowienie)
434 references Zamowienie (Id_zamowienie)
435go
436
437alter table R_3
438 add constraint FK_R_3_R_4_PRODUKT foreign key (Id_produkt)
439 references Produkt (Id_produkt)
440go
441
442alter table Zamowienie
443 add constraint FK_ZAMOWIEN_R_2_FAKTURA foreign key (Id_faktura)
444 references Faktura (Id_faktura)
445go
446
447alter table Zamowienie
448 add constraint FK_ZAMOWIEN_R_8_KLIENT foreign key (Id_klient)
449 references Klient (Id_klient)
450go
451
452alter table Zamowienie
453 add constraint FK_ZAMOWIEN_R_9_PRACOWNI foreign key (Id_pracownik)
454 references Pracownik (Id_pracownik)
455go