· 5 years ago · May 17, 2020, 09:12 PM
1-- Database generated with pgModeler (PostgreSQL Database Modeler).
2-- pgModeler version: 0.9.1
3-- PostgreSQL version: 10.0
4-- Project Site: pgmodeler.io
5-- Model Author: ---
6
7
8-- Database creation must be done outside a multicommand file.
9-- These commands were put in this file only as a convenience.
10-- -- object: company | type: DATABASE --
11-- -- DROP DATABASE IF EXISTS company;
12-- CREATE DATABASE company
13-- ENCODING = 'UTF8'
14-- LC_COLLATE = 'en_US.utf8'
15-- LC_CTYPE = 'en_US.utf8'
16-- TABLESPACE = pg_default
17-- OWNER = postgres;
18-- -- ddl-end --
19--
20
21-- object: public.suppliers_supplierid_seq | type: SEQUENCE --
22-- DROP SEQUENCE IF EXISTS public.suppliers_supplierid_seq CASCADE;
23CREATE SEQUENCE public.suppliers_supplierid_seq
24 INCREMENT BY 1
25 MINVALUE 1
26 MAXVALUE 2147483647
27 START WITH 1
28 CACHE 1
29 NO CYCLE
30 OWNED BY NONE;
31-- ddl-end --
32ALTER SEQUENCE public.suppliers_supplierid_seq OWNER TO postgres;
33-- ddl-end --
34
35-- object: public.suppliers | type: TABLE --
36-- DROP TABLE IF EXISTS public.suppliers CASCADE;
37CREATE TABLE public.suppliers(
38 supplierid integer NOT NULL DEFAULT nextval('public.suppliers_supplierid_seq'::regclass),
39 contactname character varying(20),
40 defactoaddress character varying(20),
41 dejuroaddress character varying(20),
42 phonenumber integer,
43 email character varying(20),
44 CONSTRAINT suppliers_pkey PRIMARY KEY (supplierid)
45
46);
47-- ddl-end --
48ALTER TABLE public.suppliers OWNER TO postgres;
49-- ddl-end --
50
51-- object: public.terms_termsid_seq | type: SEQUENCE --
52-- DROP SEQUENCE IF EXISTS public.terms_termsid_seq CASCADE;
53CREATE SEQUENCE public.terms_termsid_seq
54 INCREMENT BY 1
55 MINVALUE 1
56 MAXVALUE 2147483647
57 START WITH 1
58 CACHE 1
59 NO CYCLE
60 OWNED BY NONE;
61-- ddl-end --
62ALTER SEQUENCE public.terms_termsid_seq OWNER TO postgres;
63-- ddl-end --
64
65-- object: public.terms | type: TABLE --
66-- DROP TABLE IF EXISTS public.terms CASCADE;
67CREATE TABLE public.terms(
68 termsid integer NOT NULL DEFAULT nextval('public.terms_termsid_seq'::regclass),
69 status character varying(20),
70 validity timestamp,
71 termsprice integer,
72 supplierid integer,
73 employeecode integer,
74 content text,
75 offerprice integer,
76 CONSTRAINT terms_pkey PRIMARY KEY (termsid)
77
78);
79-- ddl-end --
80ALTER TABLE public.terms OWNER TO postgres;
81-- ddl-end --
82
83-- object: public.employees_employeecode_seq | type: SEQUENCE --
84-- DROP SEQUENCE IF EXISTS public.employees_employeecode_seq CASCADE;
85CREATE SEQUENCE public.employees_employeecode_seq
86 INCREMENT BY 1
87 MINVALUE 1
88 MAXVALUE 2147483647
89 START WITH 1
90 CACHE 1
91 NO CYCLE
92 OWNED BY NONE;
93-- ddl-end --
94ALTER SEQUENCE public.employees_employeecode_seq OWNER TO postgres;
95-- ddl-end --
96
97-- object: public.employees | type: TABLE --
98-- DROP TABLE IF EXISTS public.employees CASCADE;
99CREATE TABLE public.employees(
100 employeecode integer NOT NULL DEFAULT nextval('public.employees_employeecode_seq'::regclass),
101 name character varying(20),
102 "position" character varying(20),
103 CONSTRAINT employees_pkey PRIMARY KEY (employeecode)
104
105);
106-- ddl-end --
107ALTER TABLE public.employees OWNER TO postgres;
108-- ddl-end --
109
110-- object: public.invoice_invoiceid_seq | type: SEQUENCE --
111-- DROP SEQUENCE IF EXISTS public.invoice_invoiceid_seq CASCADE;
112CREATE SEQUENCE public.invoice_invoiceid_seq
113 INCREMENT BY 1
114 MINVALUE 1
115 MAXVALUE 2147483647
116 START WITH 1
117 CACHE 1
118 NO CYCLE
119 OWNED BY NONE;
120-- ddl-end --
121ALTER SEQUENCE public.invoice_invoiceid_seq OWNER TO postgres;
122-- ddl-end --
123
124-- object: public.invoice | type: TABLE --
125-- DROP TABLE IF EXISTS public.invoice CASCADE;
126CREATE TABLE public.invoice(
127 invoiceid integer NOT NULL DEFAULT nextval('public.invoice_invoiceid_seq'::regclass),
128 inn integer,
129 kpp character varying(20),
130 suppliername character varying(20),
131 employeecode integer,
132 orderid integer,
133 supplierid integer,
134 paymentid integer,
135 paymentdate timestamp,
136 CONSTRAINT invoice_pkey PRIMARY KEY (invoiceid)
137
138);
139-- ddl-end --
140ALTER TABLE public.invoice OWNER TO postgres;
141-- ddl-end --
142
143-- object: public.supplierorder_orderid_seq | type: SEQUENCE --
144-- DROP SEQUENCE IF EXISTS public.supplierorder_orderid_seq CASCADE;
145CREATE SEQUENCE public.supplierorder_orderid_seq
146 INCREMENT BY 1
147 MINVALUE 1
148 MAXVALUE 2147483647
149 START WITH 1
150 CACHE 1
151 NO CYCLE
152 OWNED BY NONE;
153-- ddl-end --
154ALTER SEQUENCE public.supplierorder_orderid_seq OWNER TO postgres;
155-- ddl-end --
156
157-- object: public.supplierorder | type: TABLE --
158-- DROP TABLE IF EXISTS public.supplierorder CASCADE;
159CREATE TABLE public.supplierorder(
160 orderid integer NOT NULL DEFAULT nextval('public.supplierorder_orderid_seq'::regclass),
161 employeecode integer,
162 supplierid integer,
163 notes character varying(20),
164 description character varying(20),
165 executiondate timestamp,
166 CONSTRAINT supplierorder_pkey PRIMARY KEY (orderid)
167
168);
169-- ddl-end --
170ALTER TABLE public.supplierorder OWNER TO postgres;
171-- ddl-end --
172
173-- object: public.orderitems | type: TABLE --
174-- DROP TABLE IF EXISTS public.orderitems CASCADE;
175CREATE TABLE public.orderitems(
176 productcode integer NOT NULL,
177 orderid integer NOT NULL,
178 amount integer,
179 price integer,
180 CONSTRAINT orderitems_pkey PRIMARY KEY (orderid,productcode)
181
182);
183-- ddl-end --
184ALTER TABLE public.orderitems OWNER TO postgres;
185-- ddl-end --
186
187-- object: public.suppliesshedule_supplyid_seq | type: SEQUENCE --
188-- DROP SEQUENCE IF EXISTS public.suppliesshedule_supplyid_seq CASCADE;
189CREATE SEQUENCE public.suppliesshedule_supplyid_seq
190 INCREMENT BY 1
191 MINVALUE 1
192 MAXVALUE 2147483647
193 START WITH 1
194 CACHE 1
195 NO CYCLE
196 OWNED BY NONE;
197-- ddl-end --
198ALTER SEQUENCE public.suppliesshedule_supplyid_seq OWNER TO postgres;
199-- ddl-end --
200
201-- object: public.suppliesshedule | type: TABLE --
202-- DROP TABLE IF EXISTS public.suppliesshedule CASCADE;
203CREATE TABLE public.suppliesshedule(
204 supplyid integer NOT NULL DEFAULT nextval('public.suppliesshedule_supplyid_seq'::regclass),
205 termsid integer,
206 supplydate timestamp,
207 orderid integer,
208 productcode integer,
209 description character varying(20),
210 CONSTRAINT suppliesshedule_pkey PRIMARY KEY (supplyid)
211
212);
213-- ddl-end --
214ALTER TABLE public.suppliesshedule OWNER TO postgres;
215-- ddl-end --
216
217-- object: public.materials_productcode_seq | type: SEQUENCE --
218-- DROP SEQUENCE IF EXISTS public.materials_productcode_seq CASCADE;
219CREATE SEQUENCE public.materials_productcode_seq
220 INCREMENT BY 1
221 MINVALUE 1
222 MAXVALUE 2147483647
223 START WITH 1
224 CACHE 1
225 NO CYCLE
226 OWNED BY NONE;
227-- ddl-end --
228ALTER SEQUENCE public.materials_productcode_seq OWNER TO postgres;
229-- ddl-end --
230
231-- object: public.materials | type: TABLE --
232-- DROP TABLE IF EXISTS public.materials CASCADE;
233CREATE TABLE public.materials(
234 productcode integer NOT NULL DEFAULT nextval('public.materials_productcode_seq'::regclass),
235 productname character varying(20),
236 productweight integer,
237 producttype character varying(20),
238 description text,
239 CONSTRAINT materials_pkey PRIMARY KEY (productcode)
240
241);
242-- ddl-end --
243ALTER TABLE public.materials OWNER TO postgres;
244-- ddl-end --
245
246-- object: public.defectivegoods | type: TABLE --
247-- DROP TABLE IF EXISTS public.defectivegoods CASCADE;
248CREATE TABLE public.defectivegoods(
249 orderid integer NOT NULL,
250 productcode integer NOT NULL,
251 count integer,
252 amount character varying(20),
253 cause character varying(20),
254 CONSTRAINT defectivegoods_pkey PRIMARY KEY (orderid,productcode)
255
256);
257-- ddl-end --
258ALTER TABLE public.defectivegoods OWNER TO postgres;
259-- ddl-end --
260
261-- object: public.agreement_agreementid_seq | type: SEQUENCE --
262-- DROP SEQUENCE IF EXISTS public.agreement_agreementid_seq CASCADE;
263CREATE SEQUENCE public.agreement_agreementid_seq
264 INCREMENT BY 1
265 MINVALUE 1
266 MAXVALUE 2147483647
267 START WITH 1
268 CACHE 1
269 NO CYCLE
270 OWNED BY NONE;
271-- ddl-end --
272ALTER SEQUENCE public.agreement_agreementid_seq OWNER TO postgres;
273-- ddl-end --
274
275-- object: public.agreement | type: TABLE --
276-- DROP TABLE IF EXISTS public.agreement CASCADE;
277CREATE TABLE public.agreement(
278 employeecode integer,
279 supplierid integer,
280 agreementid integer NOT NULL DEFAULT nextval('public.agreement_agreementid_seq'::regclass),
281 agreementstatus character varying(20),
282 date timestamp,
283 subject text,
284 sum integer,
285 status character varying(20),
286 CONSTRAINT agreement_pkey PRIMARY KEY (agreementid)
287
288);
289-- ddl-end --
290ALTER TABLE public.agreement OWNER TO postgres;
291-- ddl-end --
292
293-- object: terms_employeecode_fkey | type: CONSTRAINT --
294-- ALTER TABLE public.terms DROP CONSTRAINT IF EXISTS terms_employeecode_fkey CASCADE;
295ALTER TABLE public.terms ADD CONSTRAINT terms_employeecode_fkey FOREIGN KEY (employeecode)
296REFERENCES public.employees (employeecode) MATCH SIMPLE
297ON DELETE NO ACTION ON UPDATE NO ACTION;
298-- ddl-end --
299
300-- object: terms_supplierid_fkey | type: CONSTRAINT --
301-- ALTER TABLE public.terms DROP CONSTRAINT IF EXISTS terms_supplierid_fkey CASCADE;
302ALTER TABLE public.terms ADD CONSTRAINT terms_supplierid_fkey FOREIGN KEY (supplierid)
303REFERENCES public.suppliers (supplierid) MATCH SIMPLE
304ON DELETE NO ACTION ON UPDATE NO ACTION;
305-- ddl-end --
306
307-- object: invoice_employeecode_fkey | type: CONSTRAINT --
308-- ALTER TABLE public.invoice DROP CONSTRAINT IF EXISTS invoice_employeecode_fkey CASCADE;
309ALTER TABLE public.invoice ADD CONSTRAINT invoice_employeecode_fkey FOREIGN KEY (employeecode)
310REFERENCES public.employees (employeecode) MATCH SIMPLE
311ON DELETE NO ACTION ON UPDATE NO ACTION;
312-- ddl-end --
313
314-- object: invoice_orderid_fkey | type: CONSTRAINT --
315-- ALTER TABLE public.invoice DROP CONSTRAINT IF EXISTS invoice_orderid_fkey CASCADE;
316ALTER TABLE public.invoice ADD CONSTRAINT invoice_orderid_fkey FOREIGN KEY (orderid)
317REFERENCES public.supplierorder (orderid) MATCH SIMPLE
318ON DELETE NO ACTION ON UPDATE NO ACTION;
319-- ddl-end --
320
321-- object: invoice_supplierid_fkey | type: CONSTRAINT --
322-- ALTER TABLE public.invoice DROP CONSTRAINT IF EXISTS invoice_supplierid_fkey CASCADE;
323ALTER TABLE public.invoice ADD CONSTRAINT invoice_supplierid_fkey FOREIGN KEY (supplierid)
324REFERENCES public.suppliers (supplierid) MATCH SIMPLE
325ON DELETE NO ACTION ON UPDATE NO ACTION;
326-- ddl-end --
327
328-- object: supplierorder_employeecode_fkey | type: CONSTRAINT --
329-- ALTER TABLE public.supplierorder DROP CONSTRAINT IF EXISTS supplierorder_employeecode_fkey CASCADE;
330ALTER TABLE public.supplierorder ADD CONSTRAINT supplierorder_employeecode_fkey FOREIGN KEY (employeecode)
331REFERENCES public.employees (employeecode) MATCH SIMPLE
332ON DELETE NO ACTION ON UPDATE NO ACTION;
333-- ddl-end --
334
335-- object: supplierorder_supplierid_fkey | type: CONSTRAINT --
336-- ALTER TABLE public.supplierorder DROP CONSTRAINT IF EXISTS supplierorder_supplierid_fkey CASCADE;
337ALTER TABLE public.supplierorder ADD CONSTRAINT supplierorder_supplierid_fkey FOREIGN KEY (supplierid)
338REFERENCES public.suppliers (supplierid) MATCH SIMPLE
339ON DELETE NO ACTION ON UPDATE NO ACTION;
340-- ddl-end --
341
342-- object: orderitems_orderid_fkey | type: CONSTRAINT --
343-- ALTER TABLE public.orderitems DROP CONSTRAINT IF EXISTS orderitems_orderid_fkey CASCADE;
344ALTER TABLE public.orderitems ADD CONSTRAINT orderitems_orderid_fkey FOREIGN KEY (orderid)
345REFERENCES public.supplierorder (orderid) MATCH SIMPLE
346ON DELETE NO ACTION ON UPDATE NO ACTION;
347-- ddl-end --
348
349-- object: orderitems_productcode_fkey | type: CONSTRAINT --
350-- ALTER TABLE public.orderitems DROP CONSTRAINT IF EXISTS orderitems_productcode_fkey CASCADE;
351ALTER TABLE public.orderitems ADD CONSTRAINT orderitems_productcode_fkey FOREIGN KEY (productcode)
352REFERENCES public.materials (productcode) MATCH SIMPLE
353ON DELETE NO ACTION ON UPDATE NO ACTION;
354-- ddl-end --
355
356-- object: suppliesshedule_termsid_fkey | type: CONSTRAINT --
357-- ALTER TABLE public.suppliesshedule DROP CONSTRAINT IF EXISTS suppliesshedule_termsid_fkey CASCADE;
358ALTER TABLE public.suppliesshedule ADD CONSTRAINT suppliesshedule_termsid_fkey FOREIGN KEY (termsid)
359REFERENCES public.terms (termsid) MATCH SIMPLE
360ON DELETE NO ACTION ON UPDATE NO ACTION;
361-- ddl-end --
362
363-- object: suppliesshedule_orderid_fkey | type: CONSTRAINT --
364-- ALTER TABLE public.suppliesshedule DROP CONSTRAINT IF EXISTS suppliesshedule_orderid_fkey CASCADE;
365ALTER TABLE public.suppliesshedule ADD CONSTRAINT suppliesshedule_orderid_fkey FOREIGN KEY (orderid)
366REFERENCES public.supplierorder (orderid) MATCH SIMPLE
367ON DELETE NO ACTION ON UPDATE NO ACTION;
368-- ddl-end --
369
370-- object: suppliesshedule_productcode_fkey | type: CONSTRAINT --
371-- ALTER TABLE public.suppliesshedule DROP CONSTRAINT IF EXISTS suppliesshedule_productcode_fkey CASCADE;
372ALTER TABLE public.suppliesshedule ADD CONSTRAINT suppliesshedule_productcode_fkey FOREIGN KEY (productcode)
373REFERENCES public.materials (productcode) MATCH SIMPLE
374ON DELETE NO ACTION ON UPDATE NO ACTION;
375-- ddl-end --
376
377-- object: defectivegoods_orderid_fkey | type: CONSTRAINT --
378-- ALTER TABLE public.defectivegoods DROP CONSTRAINT IF EXISTS defectivegoods_orderid_fkey CASCADE;
379ALTER TABLE public.defectivegoods ADD CONSTRAINT defectivegoods_orderid_fkey FOREIGN KEY (orderid)
380REFERENCES public.supplierorder (orderid) MATCH SIMPLE
381ON DELETE NO ACTION ON UPDATE NO ACTION;
382-- ddl-end --
383
384-- object: defectivegoods_productcode_fkey | type: CONSTRAINT --
385-- ALTER TABLE public.defectivegoods DROP CONSTRAINT IF EXISTS defectivegoods_productcode_fkey CASCADE;
386ALTER TABLE public.defectivegoods ADD CONSTRAINT defectivegoods_productcode_fkey FOREIGN KEY (productcode)
387REFERENCES public.materials (productcode) MATCH SIMPLE
388ON DELETE NO ACTION ON UPDATE NO ACTION;
389-- ddl-end --
390
391-- object: agreement_employeecode_fkey | type: CONSTRAINT --
392-- ALTER TABLE public.agreement DROP CONSTRAINT IF EXISTS agreement_employeecode_fkey CASCADE;
393ALTER TABLE public.agreement ADD CONSTRAINT agreement_employeecode_fkey FOREIGN KEY (employeecode)
394REFERENCES public.employees (employeecode) MATCH SIMPLE
395ON DELETE NO ACTION ON UPDATE NO ACTION;
396-- ddl-end --
397
398-- object: agreement_supplierid_fkey | type: CONSTRAINT --
399-- ALTER TABLE public.agreement DROP CONSTRAINT IF EXISTS agreement_supplierid_fkey CASCADE;
400ALTER TABLE public.agreement ADD CONSTRAINT agreement_supplierid_fkey FOREIGN KEY (supplierid)
401REFERENCES public.suppliers (supplierid) MATCH SIMPLE
402ON DELETE NO ACTION ON UPDATE NO ACTION;
403-- ddl-end --