oracle——完整性约束条件(约束概念性问题、创建约束、添加约束、查询约束)约束：非空、唯一、主键、外键、默认值、检查

最新推荐文章于 2023-05-31 23:01:52 发布

爱睡觉的小馨

最新推荐文章于 2023-05-31 23:01:52 发布

阅读量1.9k

点赞数 2

分类专栏： Oracle 文章标签： oracle 数据库 database

本文链接：https://blog.csdn.net/qq_45696288/article/details/120555102

版权

Oracle 专栏收录该内容

53 篇文章 17 订阅

订阅专栏

多嘴一句：
方案即模式，scott就是方案名，用户和方案没有明显区分。P79

约束概念性问题
约束就是保证数据库完整性的方法，数据库的完整性就是为了保证数据的正确性。
概念：在数据表上强制执行的一些数据校验规则。当执行增、删、改操作时，数据必须符合在相关字段上设置的校验规则
作用：保证数据的准确性
内容：非空、唯一、主键、外键和检查
分类：列级约束＼表级约束

约束其实就是一种规则，或者说是条件

建表后：如果在一个表中存在成绩101，如果想设置约束成绩≤100，那么创建约束一定是失败的，因为必须先把不满足约束的记录删除才能插入约束。

建表时：如果在建表的时候同时设置约束成绩≤100，那么在插入记录的时候，将成绩设置为101，那么一定是插入不进去的。

除了非空、唯一、主键、外键和检查约束外，其实还新增加了默认约束和自动增加约束，一会举几个例子稍做了解就好。

约束是可查的哦

创建约束

约束条件是依附于表的，可以在建表时创建，也可以在建表后创建。
它是一种规则，和表一样存放在数据字典中。也需要命名，或者由系统自动命名。如果是用户命名，一般遵循以下格式：表名_列名_约束条件类型；而Oracle命名，则按照SYS_Cn的格式，其中n为一串数字。

语法
建表时：直接加在列名后面
建表后：使用alter table 表名＋add constraint 约束名　约束类型（字段名）
或使用修改方式添加约束
Alter table 表名 modify 字段名约束类型

含义：(not null)确保该字段值不为空。默认情况下，任何字段值都可以为空
操作：

在建表时创建非空约束
create table book(bid number(4) NOT NULL,title varchar2(20));
insert into book(title) values(‘tmie’);
建表后创建约束
alter table book modify(title NOT null);
alter table book modify(title varchar(2) NOT null);

alter table 表名 add constraint 约束名约束类型(字段名)；
使用add添加约束不适合用于添加非空约束
modify几乎适用于所有的约束

创建表：
create table book(bid number(3) not null,bname varchar2(20));
其实，在这里 not null 就是一种约束

desc book;
查看表结构，显示bid not null

创建约束：
alter table book modify bname not null;
添加的是非空类型的约束，只能使用modify，不能使用add

验证约束是否已经起作用：
insert into book values(null,‘aa’);
这个时候发现无法将null插入，因为已经设置了约束条件不能为null

添加列时，也可以直接添加约束

alter table book add author varchar2(20) not null;
注意：
有的人可能被误导了，不是说添加非空约束的时候不能使用add进行添加约束吗，为什么这里可以啊，你仔细看一下，这里的add是用来修改表结构，用来添加一列的，添加列的同时默认设置一个not null 非空约束，而不是通过add单独来加的约束，单独使用add添加约束的话，那么肯定不能使用add，只能使用motify。
再说明：创建列的时候数据类型必须设置

在数据字典中查询约束

desc user_constraints;

select owner,constraint_name,constraint_type,table_name from user_constraints where table_name=‘BOOK’;

col owner for a15
col constraint_name for a20
col constraint_type for a20
col table_name for a20

查看约束时，C代表非空约束，U代表唯一约束

注意一句话：
非空约束不允许为空，但是可以重复
唯一约束可以有空值，但不能有重复

建表时同时设置约束
create table stu(sid number(3) constraint c_sid not null,sname varchar2(20),email varchar2(50),constraint u_email unique(email));
c_sid是设置的sid的非空约束名字，not null表示非空约束，u_email是设置的email的唯一约束名字，unique表示唯一约束

查看一下刚刚设置好的约束：
select owner,constraint_name,constraint_type,table_name from user_constraints where table_name=‘stu’;

添加列时，也可以直接添加约束
alter table book add author varchar2(10) NOT NULL;
在数据字典中查询约束
desc user_constraints;
select owner,constraint_name,constraint_type,table_name from user_constraints where table_name=‘BOOK’;
注意：如果建表后为某列添加非空约束，并且表中该列数据已经存在null值，则向该列添加约束的行为将失败。这是因为列应用非空约束时，Oracle会检查表中所有的行，以验证所有行在对应列上是否存在空值。

alter table stu add constraint u_name unique (sname);
修改表（增加一列），创建约束

insert into stu values (101,‘aa’,‘dd’);
此时是插进去的

insert into stu values (101,‘aa’,‘dd’);
再输入一遍，我们发现提示违反唯一约束条件

说明：当add添加多个字段时，括号是一定不可以省略的。

约束－－唯一

含义：(unique)保证字段或字段的组合不出现重复值。当给表的某个列定义了唯一约束条件时，该列的值不允许重复，但允许值是空值。

操作：

建表时：创建唯一约束
Create table student(sid number(4) constraint student_sid_nn not null,user_name varchar2(10),
email varchar2(20),constraint student_email_uk unique(email));
建表后：创建唯一约束
alter table student add constraint student_user_name_uk unique(user_name);
定义唯一约束后，字段值不允许重复
insert into student values(1001,‘jone’,‘3245@qq.com’);
insert into student values(1001,‘nice’,‘3245@qq.com’);
非空允许重复，所以第一个值没错。第三个值错了。
添加列时指定约束
alter table student add salary number(5,2) unique;
可以在多列上建立约束，必须是表级约束。
alter table student add(ri varchar2(4),qq varchar2(3));
alter table student add constraint ss unique(ri,qq);
在数据字典中查看约束信息
select owner,constraint_name,constraint_type from user_constraints
where table_name=‘STUDENT’;

约束－－主键

含义：从功能上看相当于非空且唯一。主键字段可以是单字段或多字段组合，意味着在主键约束下的单字段或多字段组合上不允许有空值，也不允许有重复值。
主键可以用来在表中唯一确定一行数据
在一个表上只允许建立一个主键，而其他约束条件则没有明确的个数限制。

操作：

建表同时创建列级主键约束，由系统自动赋予约束条件名称
Create table student(sid number(4) primary key,user_name varchar2(10));
建表同时创建表级主键约束，自定义约束条件名称
Drop table student;
Create table student(sid number(4),user_name varchar2(10),
constraint student_sid_pk primary key(sid));
建表后创建表级主键约束，自定义约束条件名称
Create table student1(sid number(4),user_name varchar2(10));

alter table student1 add constraint student1_sid_pk primary key(sid);
验证＼查询(自己练习)

说明：主键是非空且唯一的

在SCOTT下：

建表：
create table book(bid number(3) primary key, bname varchar2(20));
这是设置的列级约束，primary key表示主键

对于主键，可以不用起约束的名字

建表：
create table stu(sid number(3),sname varchar2(20),constraint pk_sid primary key(sid));
表级约束
constraint pk_sid primary key(sid)是用来设置约束的

约束－－默认值

创建表时创建：
Create table table_name(columnu data_type default value)
修改表时创建：
Alter table table_name modify column data_type defaule value;

默认值约束是不能写约束名字的，修改表时用modify，不能用add

drop table stu;

create table stu(sid number(3),seex char(3) default ‘男’,score number);
default ‘男’——是进行默认值的设置，如果添加的时候seex不设置值的话，那么就使用默认值进行插入

insert into stu(sid,score) values (111,80);

select * from stu;
插入时没有设置值，那么直接使用默认值，插入数据时插入值，那么默认值就不会生效。

alter table stu modify score default 60;
表已更改，default 60设置默认值

约束－－外键

含义：该条件定义在两个表的两个字段或一个表的两个字段上，用于保证相关两个字段的关系。简单来说，就是外键用于与另一张表的关联。

条件
外键约束条件包括两个方面的数据约束：
1. 子表上定义的外键的列值，必须从主表被参照的列值中选取，或者为null
2. 当主表参照列的值被子表参照时，主表的该行记录不允许被删除。

操作：

创建外键约束：
（1）主表要有主键：
（2）子表上对应主表主键的列建立外键约束
（3）验证：
不能删除主表上的值（如果子表使用了这个值）
子表中也不能插入主表主键没有的值
子表中可以插入空值（主表主键上对应的列）

(1) alter table dept2 add constraint dept2_deptno_pk primary key(deptno);
(2) alter table employee add constraint employee_deptno_fk foreign key(deptno) references dept2(deptno);

在数据字典中查询
select constraint_name,constraint_type from user_constraints where table_name=‘EMPLOYEE’;
删除操作中关键字on的使用
在创建约束时指定
（1）禁止删除，这是oracle默认的。
（2）on delete casecade：用于指定级联删除选项。
（3）on delete set null：用于删除主表时，将从表的外键列的值设置为null

外键，是另一个表的主键，设置外键，这一般是两个表

select * from amp;

set line size 120;

set pagesize 30;

select * from amp;

select * from demo;

有主键的表叫父表(主表)，外键的表是子表(从表)

select owner, constraint_name,constraint_type,table_name from user_constraints ;
上面是在SCOTT/tiger 用户下，如果想在管理员下做的话，需要用下面的命令，而不需要使用此命令
conn system/root
select owner, constraint_name,constraint_type,table_name from dba_constraints where owner=‘C##SCOTT’;

create table emp2 as select * from emp;
create table emp2 as select * from c##scott.emp;(管理员下创建的方法)

create table dept2 as select * from dept;
这样创建表不复制约束条件

select owner, constraint_name,constraint_type,table_name from user_constraints;
select owner, constraint_name,constraint_type,table_name from dba_constraints where table_name=‘emp’ or table_name=‘dept’;（管理员身份下的查询方法）

alter table dept add primary key(deptno);
在主表上加主键

alter table emp add foreign key(deptno) references dept(deptno);
添加一个外键

select owner, constraint_name,constraint_type,table_name from dba_constraints where table_name=‘emp’;
显示约束条件

create table emp3(empno number(4) primary key,deptno number(2) references dept);
创建表时创建主键、外键

insert into emp(empno,deptno) values(2222,50);
提示违反完整性束条件，因为deptno的值必须要引用dept中的值

delete from dept where deptno=10;
提示违反完整约束条件，因为emp中有deptno=10的记录，故不能删

create table emp4 as select * from emp;
create table dept4 as select * from dept4;
创建的emp4与dept4不会复制emp与dept表的约束条件，故没有主键和外键。

alter table dept4 add primary key (deptno);
设置主键

alter table emp4 add foreign key(deptno) references dept4 on delete casecade;
级联删除（有外键才有级联）,设置外键

select * from emp4;

select * from dept4;

delete from dept4 where deptno =10;
原本是不能删除的，但是设置了级联删除，所以可以删除，但是不推荐使用

约束－－检查

含义：强制在字段上的每个值都要满足check中定义的条件。当定义了check约束的列新增或修改数据时，数据必须符合check约束中定义的条件。

操作

建表时创建：
create table usertable1(userid number(3),username varchar2(10),salary number(7,2) check (salary>800));
建表后创建　：
alter table usertable add constraint usertable_salary check (salary>800);
验证：
insert into usertable values(101,‘rowd’,897);//执行成功
insert into usertable values(102,‘rtyu’,798);//执行失败

alter table 表名 add constraint pk_birth check (c_birth>=to_date(‘2000-01-01’,‘yyyy-mm-dd’));

insert into customers_info values(12345,‘a’,123,‘18-1月-2001’,4);
insert into customers_info values(13,‘b’,123,‘18-1月-1999’,5);

禁用和启用约束条件

（一）作用及意义：
禁用约束是指让约束暂时失效，比如需要批量导入数据时，为了避免约束检查影响导入速度，可以先将约束禁用，导入完成后再将约束启用。
（二）语法：
Alter table 表名 disable|enable constraint constraint_name [cascade]

（三）操作：
（1）先查看约束：select table_name,constraint_name from user_constraints;
（2）禁用：alter table student disable constraint 约束名
（3）验证：
（4）重新启用：
alter table student enable constraint student_sid_pk;
执行失败，为什么？
修改约束名称
alter table 表名 rename constraint旧名 to 新名
删除约束
alter table 表名 drop constraint 约束名
Alter table 表名 drop primary key
Alter table表名 drop unique（字段名）
Alter table 表名 modify 字段名　数据类型　null;

约束创建完就是可用的。

重新启动约束一定要先把不符合约束条件的记录删除

非空约束只能使用alter table 表名 modify 字段名数据类型 null;