1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
CREATE TABLE users (
user_id INT(8) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(30) NOT NULL,
user_pass VARCHAR(255) NOT NULL,
user_level INT(8) NOT NULL DEFAULT 0,
user_date DATETIME NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
) ENGINE = InnoDB;
CREATE TABLE categories (
cat_id INT(8) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(255) NOT NULL,
cat_description VARCHAR(255) NOT NULL,
cat_thread_count INT(8) NOT NULL DEFAULT 0,
cat_post_count INT(8) NOT NULL DEFAULT 0,
UNIQUE INDEX cat_name_unique (cat_name),
PRIMARY KEY (cat_id)
) ENGINE = InnoDB;
CREATE TABLE threads (
thread_id INT(8) NOT NULL AUTO_INCREMENT,
thread_subject VARCHAR(255) NOT NULL,
thread_date_created DATETIME NOT NULL,
thread_date_lastpost DATETIME NOT NULL,
thread_category INT(8) NOT NULL,
thread_author INT(8) NOT NULL,
PRIMARY KEY (thread_id)
) ENGINE = InnoDB;
CREATE TABLE posts (
post_id INT(8) NOT NULL AUTO_INCREMENT,
post_content TEXT NOT NULL,
post_date DATETIME NOT NULL,
post_thread INT(8) NOT NULL,
post_author INT(8) NOT NULL,
PRIMARY KEY (post_id)
) ENGINE = InnoDB;
ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(cat_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE posts ADD FOREIGN KEY(post_thread) REFERENCES threads(thread_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE;
|
