建表并修改表約束

悟靜 2012-06-14

展開全文

主鍵約束 primary key
           not null
           check
           unique 唯一約束

create table student( --學(xué)生表
           xh number(4) constraint pk_stu primary key, --學(xué)號主鍵
           xm varchar2(10) constraint nn_stu not null, --姓名不能為空
           sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性別
           birthday date constraint uq_bir unique, --日期
           sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--獎學(xué)金 sal >=500 and sal <=1000
        );
      <2>建立約束的同時給約束指定名字，便于刪除
        create table cla( --班級表
          id number(2) constraint pk_cla primary key, --班級編號
          cname varchar2(20) constraint nn_cla not null --班級名字
       );

      create table stu( --學(xué)生表
          xh number(4) constraint pk_stu primary key, --學(xué)號是主鍵
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年齡在10到90之間(10<= age <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份證唯一
          classid number(2) constraint fk_stu references cla(id) -- 班級編號外鍵
           --(引用的一定是另外表的主鍵或唯一性約束的字段)
         );
3）建完表后加約束
加約束
   加主鍵
    alter table student add constraint pk_stu
    primary key (xh);
   加非空
    alter table student modify (xm not null);
   檢查約束
    alter table student add check(sex in ('男','女'));
    alter table student add constraint ck_sal check(sal between 500 and 1000));
添加主鍵
alter table cla add constraint pk_cla
       primary key (id);
加 not null
alter table cla modify
       (cname not null);

學(xué)生表student:

      create table student(
          xh number(4) ,
          xm varchar2(20) ,
          age number(2),
          birthday date,
          shenfenzheng number(18),
          classid number(2) references cla(id)
         );

加外鍵約束
alter table student add constraint fk_stu
    foreign key (classid) references cla(id);

加主鍵
alter table student add constraint pk_stu
primary key (xh);

加not null
alter table student modify(xm not null);

加檢查約束
alter table student add constraint cc_age
check (age >= 10 and age <=90);

加唯一約束
alter table student add constraint
      uq_sfz unique(shenfenzheng);

加外鍵約束
alter table student add constraint
    fk_stu foreign key (classid)
     references cla(id);