sql建表语句（含：序列、主键）

–1:创建表空间

CREATE TABLESPACE db_news
DATAFILE 'e:\jsp\db\news.dbf'
SIZE 100m;

–2:创建用户

CREATE USER news IDENTIFIED BY 123456
DEFAULT TABLESPACE db_news;

–3:授予权限

GRANT CONNECT,RESOURCE TO news;

–4:创建表
–新闻主题

create table TOPIC
(
  tid   INTEGER not null,
  tname VARCHAR2(50) not null
)
alter table TOPIC
  add constraint TID primary key (TID)

–用户表

create table NEWS_USERS
(
  usid  INTEGER not null,
  uname VARCHAR2(20) not null,
  upwd  VARCHAR2(60) not null
)
alter table NEWS_USERS
  add primary key (USID)

–新闻表

create table NEWS
(
  nid         INTEGER not null,
  ntid        INTEGER not null,
  ntitle      VARCHAR2(200) not null,
  nauthor     VARCHAR2(50) not null,
  ncreatedate DATE,
  npicpath    VARCHAR2(1000),
  ncontent    CLOB not null,
  nmodifydate DATE,
  nsummary    VARCHAR2(500) not null
)

–创建主键

alter table NEWS
  add constraint NID primary key (NID)

–创建外键

alter table NEWS
  add constraint NEWS_TOPIC foreign key (NTID)
  references TOPIC (TID) on delete cascade;

–新闻评论

create table COMMENTS
(
  cid      INTEGER not null,
  cnid     INTEGER not null,
  ccontent VARCHAR2(3000) not null,
  cdate    DATE,
  cip      VARCHAR2(100) not null,
  cauthor  VARCHAR2(100) not null
)

alter table COMMENTS
  add constraint CID primary key (CID)

alter table COMMENTS
  add constraint CIN_NID foreign key (CNID)
  references NEWS (NID) on delete cascade

–创建序列

create sequence nid_seq
increment by 1
start with 1
minvalue 1 
nomaxvalue
nocycle