· 6 years ago · Jan 05, 2020, 06:08 PM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2017 */
3/* Created on: 27.10.2019 10:47:06 */
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('Towar') and o.name = 'FK_TOWAR_RELATIONS_KATEGORI')
10alter table Towar
11 drop constraint FK_TOWAR_RELATIONS_KATEGORI
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('Towar_zamowienia') and o.name = 'FK_TOWAR_ZA_RELATIONS_ZAMOWIEN')
17alter table Towar_zamowienia
18 drop constraint FK_TOWAR_ZA_RELATIONS_ZAMOWIEN
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('Towar_zamowienia') and o.name = 'FK_TOWAR_ZA_RELATIONS_TOWAR')
24alter table Towar_zamowienia
25 drop constraint FK_TOWAR_ZA_RELATIONS_TOWAR
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('Zamowienia') and o.name = 'FK_ZAMOWIEN_RELATIONS_UZYTKOWN')
31alter table Zamowienia
32 drop constraint FK_ZAMOWIEN_RELATIONS_UZYTKOWN
33go
34
35if exists (select 1
36 from sysobjects
37 where id = object_id('Kategoria')
38 and type = 'U')
39 drop table Kategoria
40go
41
42if exists (select 1
43 from sysindexes
44 where id = object_id('Towar')
45 and name = 'Relationship_5_FK'
46 and indid > 0
47 and indid < 255)
48 drop index Towar.Relationship_5_FK
49go
50
51if exists (select 1
52 from sysobjects
53 where id = object_id('Towar')
54 and type = 'U')
55 drop table Towar
56go
57
58if exists (select 1
59 from sysindexes
60 where id = object_id('Towar_zamowienia')
61 and name = 'Relationship_6_FK'
62 and indid > 0
63 and indid < 255)
64 drop index Towar_zamowienia.Relationship_6_FK
65go
66
67if exists (select 1
68 from sysindexes
69 where id = object_id('Towar_zamowienia')
70 and name = 'Relationship_3_FK'
71 and indid > 0
72 and indid < 255)
73 drop index Towar_zamowienia.Relationship_3_FK
74go
75
76if exists (select 1
77 from sysobjects
78 where id = object_id('Towar_zamowienia')
79 and type = 'U')
80 drop table Towar_zamowienia
81go
82
83if exists (select 1
84 from sysobjects
85 where id = object_id('Uzytkownicy')
86 and type = 'U')
87 drop table Uzytkownicy
88go
89
90if exists (select 1
91 from sysindexes
92 where id = object_id('Zamowienia')
93 and name = 'Relationship_4_FK'
94 and indid > 0
95 and indid < 255)
96 drop index Zamowienia.Relationship_4_FK
97go
98
99if exists (select 1
100 from sysobjects
101 where id = object_id('Zamowienia')
102 and type = 'U')
103 drop table Zamowienia
104go
105
106/*==============================================================*/
107/* Table: Kategoria */
108/*==============================================================*/
109create table Kategoria (
110 id_kat int not null,
111 nazwa_kat varchar(50) not null,
112 constraint PK_KATEGORIA primary key (id_kat)
113)
114go
115
116/*==============================================================*/
117/* Table: Towar */
118/*==============================================================*/
119create table Towar (
120 id_tow int not null,
121 id_kat int null,
122 nazwa varchar(100) not null,
123 opis text null,
124 cena money not null,
125 ilosc_tow int not null,
126 constraint PK_TOWAR primary key (id_tow)
127)
128go
129
130/*==============================================================*/
131/* Index: Relationship_5_FK */
132/*==============================================================*/
133
134
135
136
137create nonclustered index Relationship_5_FK on Towar (id_kat ASC)
138go
139
140/*==============================================================*/
141/* Table: Towar_zamowienia */
142/*==============================================================*/
143create table Towar_zamowienia (
144 id_tow_zam int not null,
145 id_tow int null,
146 id_zam int not null,
147 ilosc int not null,
148 cena money not null,
149 constraint PK_TOWAR_ZAMOWIENIA primary key (id_tow_zam)
150)
151go
152
153/*==============================================================*/
154/* Index: Relationship_3_FK */
155/*==============================================================*/
156
157
158
159
160create nonclustered index Relationship_3_FK on Towar_zamowienia (id_zam ASC)
161go
162
163/*==============================================================*/
164/* Index: Relationship_6_FK */
165/*==============================================================*/
166
167
168
169
170create nonclustered index Relationship_6_FK on Towar_zamowienia (id_tow ASC)
171go
172
173/*==============================================================*/
174/* Table: Uzytkownicy */
175/*==============================================================*/
176create table Uzytkownicy (
177 id_user int not null,
178 imie varchar(50) not null,
179 nazwisko varchar(50) not null,
180 email varchar(50) not null,
181 haslo varchar(50) not null,
182 ulica varchar(50) not null,
183 miasto varchar(50) not null,
184 kod_pocztowy int not null,
185 permissions int not null,
186 constraint PK_UZYTKOWNICY primary key (id_user)
187)
188go
189
190/*==============================================================*/
191/* Table: Zamowienia */
192/*==============================================================*/
193create table Zamowienia (
194 id_zam int not null,
195 id_user int not null,
196 czy_zaplacono bit not null,
197 constraint PK_ZAMOWIENIA primary key (id_zam)
198)
199go
200
201/*==============================================================*/
202/* Index: Relationship_4_FK */
203/*==============================================================*/
204
205
206
207
208create nonclustered index Relationship_4_FK on Zamowienia (id_user ASC)
209go
210
211alter table Towar
212 add constraint FK_TOWAR_RELATIONS_KATEGORI foreign key (id_kat)
213 references Kategoria (id_kat)
214go
215
216alter table Towar_zamowienia
217 add constraint FK_TOWAR_ZA_RELATIONS_ZAMOWIEN foreign key (id_zam)
218 references Zamowienia (id_zam)
219go
220
221alter table Towar_zamowienia
222 add constraint FK_TOWAR_ZA_RELATIONS_TOWAR foreign key (id_tow)
223 references Towar (id_tow)
224go
225
226alter table Zamowienia
227 add constraint FK_ZAMOWIEN_RELATIONS_UZYTKOWN foreign key (id_user)
228 references Uzytkownicy (id_user)
229go