数据库创表常用约束以及案例参照

时间：2015-05-19 10:32:53 收藏：0 阅读：195

①主键 constraint pk_name primary key <column_name>
②外键 constraint fk_name foreign key(column_name) reference table_name(column_name)
③检查 constraint ck_name check (condition)
④唯一 constraint un_name unique (column_name)
⑤非空 <column name> <data type> not null,
⑥默认值<column name> <data type> default <default value>,

 


--创建火车表
create table T_TRAIN
(
  TRAINNUM     VARCHAR2(50) primary key,
  STARTSTATION VARCHAR2(50),
  ENDSTATION   VARCHAR2(50),
  STARTTIME    VARCHAR2(50),
  ENDTIME      VARCHAR2(50),
  TOTALTIME    VARCHAR2(50),
  TYPE         VARCHAR2(20), 
  OBJ          VARCHAR2(100)
)


--创建占台表
create table T_STATION
(
  TRAINNUM     VARCHAR2(50), --火车编号
  STARTSTATION VARCHAR2(50), --初始占台
  ENDSTATION   VARCHAR2(50), --结束站
  STARTTIME    VARCHAR2(50), --开始时间
  ENDTIME      VARCHAR2(50), --结束时间
  TOTALTIME    VARCHAR2(50), --总的时长
  PRICE        FLOAT, --价格
  OBJ          VARCHAR2(100), --备注
  constraint fk_TRAINNUM foreign key (TRAINNUM) references T_TRAIN(TRAINNUM)  --约束外键-关联火车表 -
)

create table T_SEAT
(
  TRAINNUM VARCHAR2(50),
  SEATTYPE    VARCHAR2(50),
  SEATNO      VARCHAR2(50),
  SEATPRICE   FLOAT,
  SEATSTATU   INTEGER,
  OBJ         VARCHAR2(100),
  constraint t_fk_TRAINNUM foreign key (TRAINNUM) references T_TRAIN(TRAINNUM)  --约束外键-关联火车表 -
)

--创建订单表
create table T_ORDER
(
  ONUMBER     VARCHAR2(50) primary key,
  TRAINNUM    VARCHAR2(50),
  STARTTIME   VARCHAR2(50),
  ENDTIME     VARCHAR2(50),
  PRICE       FLOAT,
  SEATNO      VARCHAR2(50),
  SEATTYPE    VARCHAR2(20),
  USERNAME    VARCHAR2(50),
  PHONENUMBER VARCHAR2(30),
  IDCARD      VARCHAR2(50),
  EMAIL       VARCHAR2(50),
  OBJ         VARCHAR2(100),
  constraint o_fk_TRAINNUM foreign key (TRAINNUM) references T_TRAIN(TRAINNUM)  --约束外键-关联火车表 -
)


create table T_USERINFO
(
  USERNAME    VARCHAR2(50),
  ADDRESS     VARCHAR2(100),
  EMAIL       VARCHAR2(50),
  PHONENUMBER VARCHAR2(30),
  IDCARD      VARCHAR2(50),
  OBJ         VARCHAR2(100)
)


--火车优惠
create table t_favorable
(
  TRAINNUM VARCHAR2(50),--火车编号
  STARTTIME   VARCHAR2(50), --开始时间
  ENDTIME     VARCHAR2(50), --结束时间
  discount float, --优惠折扣
  price    float, --优惠价格
  OBJ         VARCHAR2(100),--备注
   constraint f_fk_TRAINNUM foreign key (TRAINNUM) references T_TRAIN(TRAINNUM)  --约束外键-关联火车表 -
)

原文：http://blog.csdn.net/jian_csdn/article/details/45841155