oracle 创建表约束，修改，删除

 
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来打破约束，并删除