1XTS94NH

1DROP TABLE IF EXISTS member;
2DROP TABLE IF EXISTS income;
3DROP TABLE IF EXISTS expense;
4DROP TABLE IF EXISTS payment;
5
6CREATE TABLE expense (
7    id          NUMBER NOT NULL,
8    what        VARCHAR2(256),
9    recipient   VARCHAR2(256),
10    member_id   NUMBER NOT NULL,
11    payment_id  NUMBER NOT NULL
12);
13
14CREATE UNIQUE INDEX expense__idx ON
15    expense (
16        payment_id
17    ASC );
18
19ALTER TABLE expense ADD CONSTRAINT expense_pk PRIMARY KEY ( id );
20
21CREATE TABLE income (
22    id          NUMBER NOT NULL,
23    source      VARCHAR2(256),
24    payment_id  NUMBER NOT NULL,
25    member_id   NUMBER NOT NULL
26);
27
28CREATE UNIQUE INDEX income__idx ON
29    income (
30        payment_id
31    ASC );
32
33ALTER TABLE income ADD CONSTRAINT income_pk PRIMARY KEY ( id );
34
35CREATE TABLE member (
36    id    NUMBER NOT NULL,
37    name  VARCHAR2(64),
38    age   SMALLINT
39);
40
41ALTER TABLE member ADD CONSTRAINT member_pk PRIMARY KEY ( id );
42
43CREATE TABLE payment (
44    id          NUMBER NOT NULL,
45    "date"      DATE DEFAULT CURDATE(),
46    amount      NUMBER,
47    state       VARCHAR2(16) DEFAULT 'Оплачено',
48    income_id   NUMBER NOT NULL,
49    expense_id  NUMBER NOT NULL,
50    CHECK(state in ('Оплачено', 'Зарезервирован', 'Просрочен', 'Ожидание'))
51    CHECK("date" <= CURDATE())
52    CHECK(amount > 0)
53);
54
55CREATE UNIQUE INDEX payment__idx ON
56    payment (
57        expense_id
58    ASC );
59
60CREATE UNIQUE INDEX payment__idxv1 ON
61    payment (
62        income_id
63    ASC );
64
65ALTER TABLE payment ADD CONSTRAINT payment_pk PRIMARY KEY ( id );
66
67ALTER TABLE expense
68    ADD CONSTRAINT expense_member_fk FOREIGN KEY ( member_id )
69        REFERENCES member ( id ) ON DELETE CASCADE;
70
71ALTER TABLE expense
72    ADD CONSTRAINT expense_payment_fk FOREIGN KEY ( payment_id )
73        REFERENCES payment ( id ) ON DELETE CASCADE;
74
75ALTER TABLE income
76    ADD CONSTRAINT income_member_fk FOREIGN KEY ( member_id )
77        REFERENCES member ( id ) ON DELETE CASCADE;
78
79ALTER TABLE income
80    ADD CONSTRAINT income_payment_fk FOREIGN KEY ( payment_id )
81        REFERENCES payment ( id ) ON DELETE CASCADE;
82
83ALTER TABLE payment
84    ADD CONSTRAINT payment_expense_fk FOREIGN KEY ( expense_id )
85        REFERENCES expense ( id ) ON DELETE CASCADE;
86
87ALTER TABLE payment
88    ADD CONSTRAINT payment_income_fk FOREIGN KEY ( income_id )
89        REFERENCES income ( id ) ON DELETE CASCADE;
90
91
92create index member_idx on member (name)
93create index payment_date_idx on member ("date")