Oracle建表

韓風1314發表於2018-10-06
Oracle

Oracle建表
參考網址：http://www.oraclejsq.com/article/010100139.html

-- Create table
create table STUDENT.stuinfo
(
  stuid      varchar2(10) not null,--學號：`S`+班號（6位數）+學生序號（3位數）(1)
  stuname    varchar2(50) not null,--學生姓名
  sex        char(1) not null,--性別
  age        number(2) not null,--年齡
  classno    varchar2(6) not null,--班號：年級（4位數）+班級序號（2位數）
  stuaddress varchar2(100) default `地址未錄入`,--地址 (2)
  grade      char(4) not null,--年級
  enroldate  date,--入學時間
  idnumber   varchar2(18) default `身份證未採集` not null--身份證
)
tablespace USERS --(3)
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table STUDENT.stuinfo --(4)
  is `學生資訊表`;
-- Add comments to the columns 
comment on column STUDENT.stuinfo.stuid -- (5)
  is `學號`;
comment on column STUDENT.stuinfo.stuname
  is `學生姓名`;
comment on column STUDENT.stuinfo.sex
  is `學生性別`;
comment on column STUDENT.stuinfo.age
  is `學生年齡`;
comment on column STUDENT.stuinfo.classno
  is `學生班級號`;
comment on column STUDENT.stuinfo.stuaddress
  is `學生住址`;
comment on column STUDENT.stuinfo.grade
  is `年級`;
comment on column STUDENT.stuinfo.enroldate
  is `入學時間`;
comment on column STUDENT.stuinfo.idnumber
  is `身份證號`;

 

-- Create/Recreate primary, unique and foreign key constraints 
alter table STUDENT.STUINFO
  add constraint pk_stuinfo_stuid primary key (STUID);
  --把stuid單做主鍵，主鍵欄位的資料必須是唯一性的（學號是唯一的）
  
-- Create/Recreate check constraints 
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_age
  check (age>0 and age<=50);--給欄位年齡age新增約束，學生的年齡只能0-50歲之內的
  
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_sex
  check (sex=`1` or sex=`2`);
  
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_GRADE
  check (grade>=`1900` and grade<=`2999`);

相關文章