bbs.sql

来自「基于struts和ORACLE的论坛」· SQL 代码 · 共 56 行

DROP TABLE bbsuser CASCADE CONSTRAINTS;
CREATE TABLE bbsuser(	
	username	VARCHAR2 (20) 	PRIMARY KEY,
	password	VARCHAR2 (20) 	NOT NULL,
	nickname	VARCHAR2 (20) 	NOT NULL,
	sex		VARCHAR2 (2) 	NOT NULL,
	birthyear	VARCHAR2 (4) 	NOT NULL,
	birthmonth	VARCHAR2 (4) 	NOT NULL,
	birthday	VARCHAR2 (4) 	NOT NULL,
	e_mail		VARCHAR2 (50) 	NOT NULL,
	mobile		VARCHAR2 (20)  	NULL,
	signature	VARCHAR2 (250)  NULL,
	usergrade	VARCHAR2 (20)  	NULL,
	userscore	NUMBER   	NULL
);

DROP TABLE sort CASCADE CONSTRAINTS;
CREATE TABLE sort(	
	id 		NUMBER 		PRIMARY KEY,
	sortname	VARCHAR2 (20) 	NOT NULL ,
	master		VARCHAR2 (20)  	NULL 
			REFERENCES bbsuser(username) ON DELETE CASCADE
);

DROP TABLE topic CASCADE CONSTRAINTS;
CREATE TABLE topic(	
	id 		NUMBER 		PRIMARY KEY,
	topicname 	VARCHAR2 (20) 	NOT NULL ,
	topiccontent	VARCHAR2 (2000) NULL,
	owner		VARCHAR2 (20) 	NOT NULL 
			REFERENCES bbsuser(username) ON DELETE CASCADE,
	time		DATE 		NOT NULL,
	sortid 		NUMBER 		NOT NULL 
			REFERENCES sort(id) ON DELETE CASCADE
);

DROP TABLE responses CASCADE CONSTRAINTS;
CREATE TABLE responses(	
	id 		NUMBER 		PRIMARY KEY,
	title 		VARCHAR2 (20) 	NOT NULL ,
	content		VARCHAR2 (2000) NULL,
	owner		VARCHAR2 (20) 	NOT NULL 
			REFERENCES bbsuser(username) ON DELETE CASCADE,
	time		DATE 		NOT NULL,
	topicid 	NUMBER 		NOT NULL 
			REFERENCES topic(id) ON DELETE CASCADE
);

insert into bbsuser values('manager','manager','管理员','男','1970','12','10','[e-mail]','1391000000000','管理员','管理员',10);

insert into sort values(1,'Struts','');
insert into sort values(2,'Eclipse','');