Oracle中约束条件

╰半橙微兮° 2022-07-14 07:48 307阅读 0赞

**每个标题都默认表先drop table后create table**  
**1.not null 非空约束**

create table person(
    	pid number,
    	name varchar2(20) not null
    );
    insert into person(pid,name) values(1,'zhangsan');--成功插入
    --错误的数据，会收到约束限制，无法插入。

**2.primary key 主键约束**

create table person(
    	pid number primary key,
    	name varchar2(20)
    );
    insert into person(pid,name) values(1,'zhangsan');--成功插入
    insert into person(pid,name) values(1,'lisi');--主键重复了，插入失败

**3.unique 唯一约束，值不能重复（空值除外）**  
电话不重复

create table person(
    	pid number primary key,
    	name varchar2(20),
    	tel varchar2(20) unique
    );
    insert into person(pid,name,tel) values(1,'zhangsan','1234567890');--成功插入
    insert into person(pid,name,tel) values(2,'lisi','1234567890');--电话重复，操作失败

**4.check 条件约束，插入的数据必须满足某些条件**  
例如限制年龄的输入

create table person(
    	pid number primary key,
    	name varchar2(20),
    	tel varchar2(20) not null unique,
    	age number check(age between 0 and 150)
    );
    insert into person(pid,name,tel) values(1,'zhangsan','1234567890',30);--成功插入
    insert into person(pid,name,tel) values(2,'lisi','1111111',160);--年龄输入错误

**5.foreign key 外键**  
例如：有以下一种情况：一个人有很多本书

create table book(
    bid number primary key not null,
    name varchar2(30),
    --书应该属于一个人
    pid number
    );

如果使用了以上的表直接创建，则插入下面的记录有效:

insert into book(bid,name,pid) values(1001,'oracle',15);

以上的代码没有任何错误但是没有意思，因为一本书应该只属于一个人，所以在此处的pid应该对应person表中的记录。  
此时就需要外键，修改book表结构

create table book(
    	bid number primary key not null,
    	name varchar2(30),
    	--书应该属于一个人
    	pid number references person(pid) on delete cascade --on delete cascade级联删除
    	--建立约束：book_pid_fk,与person表中pid为主-外键关系
    	--constraint book_pid_fk foreign key(pid) references person(pid)
    );
    insert into book(bid,name,pid) values(1001,'oracle',15);

**6.级联删除**  
接上面5的案例，如果一个人的人员信息没有了，那么此人所拥有的书还应该存在么？  
正常来说，如果person中的一条信息没有了，则对应的book中的数据也应该同时消失。  
在之前的结构上执行delete语句，删除person表中一条记录：

delete from person where pid=12;

提示删除失败：因为book中存在此项的关联，如果person表中的一条数据删除了，则一定会影响到book表中数据的完成性，  
所以不让删除。  
如果非要删除，则应该删除book表中的对应数据，之后再删除person表中对应的数据。  
此时如果想完成删除person表的数据同时删除book表中的数据，则必须使用级联删除。  
在建立外建的时候必须指定级联删除（on delete cascade）

create table book(
    	bid number primary key not null,
    	name varchar2(50),
    	--书应该属于一个人
    	pid number,
    	--建立约束，book_pid_fk,与person表中pid为主-外键关系
    	CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) on delete cascade
    );

为新表添加约束条件

--person
    create table person(
    	pid number,
    	name varchar2(30),
    	tel varchar2(50),
    	age number
    );
    --book
    create table book(
    	bid number,
    	name varchar2(30),
    	pid number
    );

以上两张表中没有任何约束，下面通过alter命令为表添加约束  
1.为两个表添加主键  
person表pid为主键

alter table person add constraint person_pid_pk primary key(pid);

book表bid为主键

alter table book add constraint book_bid_pk primary key(bid);

2.为person表中的tel添加唯一约束

alter table person add constraint person_tel_uk unique(tel);

3.为person表中的age添加检查约束

alter table person add constraint person_age_ck check(age between 0 and 150);

4.为book表中的pid添加与person的主--外键约束，要求带级联删除

alter table book add constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade;

**7.删除约束**

alter table person drop constraint unique(tel);
    alter table book drop constraint person_book_pid_fk;

**8.启用约束**

alter table book enable constraint person_book_pid_fk;

**9.禁用约束**

alter table book disable constraint person_book_pid_fk;

**练习：**  
1.创建一张表student  
id number  
name varchar2(20)  
age number(5)  
tel varchar2(15)  
给id字段添加主键约束  
给name字段添加非空约束  
给age字段添加check约束（18-35）  
给tel添加唯一非空约束

create table student(
    	id number primary key,
    	name varchar2(20) not null,
    	age number(5) check(age between 18 and 35),
    	tel varchar2(15) unique not null
    );

2.创建一张学员兴趣表hobby  
id number(10)  
hobby\_name varchar2(20)  
sid number --学生id  
给sid字段添加外键约束，并且要带级联删除

create table hobby(
    	id number(10),
    	hobby_name varchar2(20),
    	sid number references student(id) on delete cascade
    );

3.删除掉student表中tel字段的唯一约束（先写出查看该表约束的sql）

select constraint_name,constraint_type from all_constraints where user_table=upper('student');
    alter table student drop constraint unique(tel);

4.手动添加student表中tel字段的唯一约束（约束名为：my\_constraint\_1）

alter table student add constraint my_constraint_1 unique(tel);

5.禁用约束my\_constraint\_1

alter table student disable constraint my_constraint_1;

6.启用约束 my\_constraint\_1

alter table student enable constraint my_constraint_1;