Oracle之表的约束

--1. 建表的同时创建约束
create table student_1(
  stuid number(32) primary key,--主键
  stuname varchar2(16) unique,--唯一约束
  age varchar2(4) not null,--非空约束
  gender varchar2(8) check (gender in ('男','女','保密')) --检查约束
);

insert into student_1 values(1,'张三',22,'男');--插入数据，必须符合约束
insert into student_1 values(2,'李四',22,'女');--插入数据，必须符合约束
select * from student_1;--查询

--主键约束
insert into student_1 values(1,'陈晨',21,'男');--插入数据，必须符合约束

--唯一约束
insert into student_1 values(3,'张三',21,'男');--插入数据，必须符合约束

--非空约束
insert into student_1 values(3,'陈晨',null,'男');--插入数据，必须符合约束
insert into student_1 values(3,'陈晨','','男');--插入数据，必须符合约束

--检查约束
insert into student_1 values(3,'陈晨',21,'妖怪');--插入数据，必须符合约束


--2. 建表后创建约束
--创建学生表
CREATE TABLE STUDENT ( 
  STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号 
  STUNAME VARCHAR2(10) NOT NULL, --姓名 
  GENDER VARCHAR2(2) NOT NULL, --性别 
  AGE NUMBER(2) NOT NULL, --年龄 
  SEAT NUMBER(2) NOT NULL, --座号 
  ENROLLDATE DATE, --入学时间 
  STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址 
  CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号 
);
 
--添加主键约束。constraint 关键字英文意思为约束、限制，PK_INFOS 为约束名称（自定义），primary key 约束类型
ALTER TABLE STUDENT ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID);
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女');--添加检查约束，性别只能是男或者女
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50);--添加检查约束，坐位号只能在 [0,50] 之间
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100);--与上同理
--添加检查约束，限制班号范围
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999'))
ALTER TABLE STUDENT ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME);--添加唯一约束，姓名不能重复
 
 
--为 STUDENT 表的 添加非空约束,先新增一列“备注”
alter table student add(scomment varchar(20));
select * from student;
--因为 not null 约束类型必须声明在列上,无法声明在表上,所以必须用 modify 方式来写 
alter table student modify STUADDRESS not null ;
 
--删除 student 表的 STUADDRESS 列的非空约束
alter table student modify STUADDRESS null;


/*
  外键约束时另一张表被引用的列必须是主键 或 unique 约束
*/

--创建商品类型表。商品类型与商品 1 对多 关系，商品依赖类型表，类型表为主表，商品为从表
create table productType(
  pid number(32) primary key,
  pname varchar2(16) not null
);

--创建商品表。建表的同时设置外键
create table product(
  pid number(32) primary key,
  pname varchar2(16) not null,
  price number(8,2) not null,--价格
  ptId number(32) references productType(pid)--设置外键，关联类型表的主键
);
 
--如果建表的时候没有设置外键，则修改表，为其添加外键。默认情况下，当主表记录被从表依赖时，主表记录无法删除，除非先删除依赖项
alter table product add constraints fk_ptId foreign key(ptId) references productType(pid);
--添加外键的同时设置级联删除。即主表删除记录时，会自动搜索，如果有其它记录依赖自己，则先删除依赖项，然后删除自己。
alter table product add constraints fk_ptId foreign key(ptId) references productType(pid) on delete cascade;

--查看外键名称后删除外键
alter table product drop constraints sys_c0011423;

--插入数据
insert into productType values(1,'IT');
insert into productType values(2,'书籍');
insert into productType values(3,'美食');
select * from productType;
 
insert into product values(1,'天龙八部',35.55,2);
insert into product values(2,'蚂蚁上树',66.55,3);
select * from product;
 
delete from productType where pid =1;--正常删除，因为 pid =1 没有被任何记录依赖
--如果依赖自己的外键没有设置级联删除，则这里无法删除自己，报错：违反完整约束条件，已找到子记录。除非先删除所有依赖的子记录，否则只能强制删除（不建议）
--如果依赖自己的外键设置了级联删除，则会一并删除
delete from productType where pid =2 or pid = 3;

select * from tab;
drop table product;