|
主鍵約束 primary key not null check unique 唯一約束 create table student( --學(xué)生表 xh number(4) constraint pk_stu primary key, --學(xué)號(hào)主鍵 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)--獎(jiǎng)學(xué)金 sal >=500 and sal <=1000 ); <2>建立約束的同時(shí)給約束指定名字，便于刪除 create table cla( --班級(jí)表 id number(2) constraint pk_cla primary key, --班級(jí)編號(hào) cname varchar2(20) constraint nn_cla not null --班級(jí)名字 ); create table stu( --學(xué)生表 xh number(4) constraint pk_stu primary key, --學(xué)號(hào)是主鍵 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) -- 班級(jí)編號(hào)外鍵 --(引用的一定是另外表的主鍵或唯一性約束的字段) ); 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); |
|
|
