SQL

CREATE DATABASE CODEREV;

USE CODEREV;

CREATE TABLE user(username varchar(20) UNIQUE NOT NULL PRIMARY KEY, password varchar(100) NOT NULL,dojoin date NOT NULL);

CREATE TABLE user_profile(email_id varchar(50) ,fname varchar(20), lname varchar(20), reputation int(6) NOT NULL DEFAULT 0, description varchar(1000), posts int(4) NOT NULL DEFAULT 0, comments int(4) NOT NULL DEFAULT 0, username varchar(20) UNIQUE NOT NULL PRIMARY KEY, FOREIGN KEY (username) REFERENCES user(username) ON DELETE CASCADE);

CREATE TABLE post(language varchar(20) NOT NULL, votes int(4)  NOT NULL DEFAULT 0, comments int(4) NOT  NULL DEFAULT 0, title varchar(100) NOT NULL, content text NOT NULL, time datetime NOT NULL , postid int(32) UNIQUE NOT NULL, username varchar(20) NOT NULL,PRIMARY KEY(postid),CONSTRAINT postername FOREIGN KEY(username) REFERENCES user(username) ON DELETE CASCADE);

CREATE TABLE categories(name varchar(100) UNIQUE NOT NULL, totposts int (4) NOT NULL DEFAULT 0, PRIMARY KEY(name));

CREATE TABLE comment(commentid int(32) UNIQUE NOT NULL, content varchar(1000) NOT NULL, time datetime NOT NULL,username varchar(20),postid int(32), FOREIGN KEY(username) REFERENCES user(username) ON DELETE CASCADE, FOREIGN KEY (postid) REFERENCES post(postid) ON DELETE CASCADE, PRIMARY KEY(commentid));

CREATE TABLE postvotes(username varchar(20) NOT NULL,postid int(32) NOT NULL,time datetime NOT NULL, FOREIGN KEY(username) REFERENCES user(username) ON DELETE CASCADE, FOREIGN KEY(postid)REFERENCES post(postid) ON DELETE CASCADE,constraint pk PRIMARY KEY(postid,username) );

CREATE TABLE belongstocategory(cname varchar(20) NOT NULL, postid int(32) NOT NULL, FOREIGN KEY (cname) REFERENCES categories(name) ON DELETE CASCADE, FOREIGN KEY (postid) REFERENCES post(postid) ON DELETE CASCADE);

CREATE TABLE subscribed(username varchar(20),cname varchar(100),FOREIGN KEY (username) REFERENCES user(username), FOREIGN KEY (cname) REFERENCES categories(name) ON DELETE CASCADE,constraint pk PRIMARY KEY(cname,username));