SQL> create table goods(goodsid char(8) primary key,--主键 2 goodsname varchar2(30), 3 unitprice number(10,2) check(unitprice > 0),--单价大于0 4 category varchar2(8), 5 provider varchar2(50)); Table created SQL> create table customer(customerid char(8) primary key, 2 name varchar2(20) not null,--不能为空 3 adddress varchar2(50), 4 email varchar2(50) unique,--不能重复 5 sex char(2) default '男' check (sex in ('男','女')),--男女,默认为男 6 cardid char(18)); Table created
SQL> create table purchase (customerid char(8) references customer(customerid),--外键 2 goodsid char(8) references goods(goodsid), 3 nums number(20) check(nums between 1 and 30));--约束购买数量在1-30之间 Table created
SQL> alter table goods modify goodsname not null;--修改名字也不能为空 Table altered SQL> alter table customer add constraint yueshumingzi unique(cardid);--修改身份证不能为空 Table altered
SQL> alter table customer add constraint addresscheck check( address in('烟台','青岛','潍坊','济南','德州'));--修改地址只能从这几个地方选择 Table altered
yueshumingzi和addresscheck是两个随便起的名字,删除的时候会用到
alter table customer drop constraint addresscheck;--删除名为addresscheck的约束
alter table customer drop primary key ;--删除主键
alter table cusromer drop primary key cascade;--如果主键跟别的表有关系,需要加入一个cascade来打破约束,并删除