约束
create table goods(goodsid char(8) primary key, --主键
goodsname varchar2(20),
unitprice number(10,2) check (unitprice >0), --检查
category varchar2(10),
vendor varchar2(20));
create table customer( customerid char(8) primary key,
name varchar2(30) not null,
address varchar2(30),
email varchar2(30) unique,
sex char(2) default '男' check (sex in ('男', '女')),
cardid char(10));
create table purchase( customerid char(8) references customer(customerid), --列级定义不用foreign key
goodsid char(8) references goods(goodsid),
nums number(10) check ( nums between 1 and 30));
alter table goods modify goodsname not null;
alter table customer add constraint cardunique unique(cardid);
alter table customer add constraint addresscheck check(address in('和平', '西青', '南开');
alter table customer drop constraint cardunique;
alter table customer drop primary key [cascade]; --若存在主外键关系,需要cascade
select constraint_name, constraint_type, status, validated from user_constraints where table_name='CUSTOMER';
--查看约束信息
select column_name, position from user_cons_columns where constraint_name='ADDRESSCHECK'; --查看约束列名
列级定义--在定义列的同时定义约束
create table department(deptid number(2) constraint pk_department primary key,
name varchar2(12)
loc varchar2(12));
表级定义--在定义了所有列后,再定义约束,注意not null约束只能在列级上定义
create table employ(employid number(4),
name varchar2(20),
deptid number(2),
constraint pk_emplyee primary key (employid),
constraint fk_dept foreign key (deptid) references departmt(deptid));