vqL7rEW9

1drop table if exists news_comments, news, users;
2
3create table users
4(
5    id         serial unique,
6    email      text unique
7        constraint chk_email
8            check (email LIKE '%_@__%.__%'),
9    password   text
10        constraint chk_password
11            check (length(password) >= 6 and length(password) <= 16),
12    firstname  text,
13    secondname text,
14    lastname   text,
15    bonus      int default 0
16        constraint chk_bonus
17            check (bonus >= 0)
18);
19
20create table news
21(
22    id      serial unique,
23    name    text not null,
24    date    date default current_date
25        constraint chk_date
26            check (date <= current_date),
27    picture text,
28    detail  text not null
29);
30
31create table news_comments
32(
33    id      serial unique,
34    news_id int
35        references news (id),
36    user_id int
37        references users (id),
38    date    date default current_date
39        constraint chk_date
40            check (date <= current_date),
41    comment text not null
42);