Oracle中的约束

总结：

！！！约束是用来确保数据满足业务规则的手段，是保障数据合理性的最后一道防线，是一种非常重要的数据库设计工具，它可以保证表中数据的完整性和一致性，提高数据的查询效率和性能，避免数据的错误和丢失。

约束限制插入的数据，就是可以防止有不符合规则的数据插入进来，如果不满足的话约束会阻止你的操作，也就是报错，会说插入数据不符合约束，也就是不符合规则！！！

三、约束分类

3.1. 主键约束（Primary Key Constraint）：

用于保证表中每个记录的唯一性，通常会将主键定义为表中的一个列或多个列的组合。主键约束可以确保表中每个记录都有一个唯一的标识符，常用于定义基本表的主键，起唯一标识作用，其值不能为null，也不能重复，以此来保证实体的完整性。

对于指定为primary key的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于unique所约束的唯一键，则允许为空。不能为同一个列或一组列既定义unique约束，又定义primary key约束。

注意事项：

1. 主键约束要求每个行都具有唯一标识符，因此主键列不能包含 NULL 值。
2. 主键约束可以涵盖多个列，但通常只有一个列被用作主键。
3. 定义主键时应该选择一个短、有意义且易于记忆的列名。
4. 主键列应该是简单的数据类型，如整数或字符串。
5. 主键列的值应该稳定，不应该经常更改。
6. 主键约束自动创建唯一索引，因此在选择主键列时应该考虑索引的效率和性能。
7. 在使用主键约束时，应该避免使用复杂的查询和连接操作，以提高查询效率。
8. 在插入数据时，应该确保主键列的值是唯一的，否则会触发主键冲突错误。
9. 在更新数据时，应该避免更改主键列的值，否则会破坏主键约束。
10. 在删除数据时，应该确保不会删除包含主键约束的行，否则会触发主键冲突错误。

3.2. 唯一约束（Unique Constraint）：

用于指明基本表在某一列或多个列的组合上的取值必须唯一，保证表中某个列的值是唯一的，但是允许空值，同时系统为保证其唯一性，最多只可以出现一个null值。唯一约束可以确保表中某个列的值不重复。
定义unique约束的哪些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。unique既可用于列约束，也可用于表约束。
unique用于定义列约束时，其语法格式如下：

[constraint <约束名>] unique

唯一性约束防止在列中输入重复的值，当使用唯一性约束时，需要考虑以下几个因素：

①使用唯一性约束的字段允许为空值。
②一个表中可以允许有多个唯一性约束。
③可以把唯一性约束定义在多个字段上。
④唯一性约束用于强制在指定字段上创建一个唯一性索引。

同时unique约束是用来确保不受主键约束列上的数据的唯一性.
unique与primary key的区别在于:

（1）unique约束主要用于非主键的一列或多列上要求数据唯一.
（2）unique约束允许该列上存在null值,而主键决不允许出现.
（3）可以在一个表创建多个unique约束,而在一个表上只能够设置一个主键primary key约束

注意事项：

1. 唯一约束要求每个行都具有唯一标识符，但允许 NULL 值存在。
2. 唯一约束可以涵盖多个列，但通常只有一个列被用作唯一标识符。
3. 定义唯一约束时应该选择一个短、有意义且易于记忆的列名。
4. 唯一约束列应该是简单的数据类型，如整数或字符串。
5. 唯一约束列的值应该稳定，不应该经常更改。
6. 唯一约束自动创建唯一索引，因此在选择唯一约束列时应该考虑索引的效率和性能。
7. 在使用唯一约束时，应该避免使用复杂的查询和连接操作，以提高查询效率。
8. 在插入数据时，应该确保唯一约束列的值是唯一的，否则会触发唯一约束冲突错误。
9. 在更新数据时，应该避免更改唯一约束列的值，否则会破坏唯一约束。
10. 在删除数据时，应该确保不会删除包含唯一约束的行，否则会触发唯一约束冲突错误。

3.3. 非空约束（Not Null Constraint）：

用于保证表中某个列不允许为空值。非空约束可以确保表中某个列的值不为空。
null值不是0也不是空白，更不是填入字符串“null”，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入才有意义的时候，则可以设置为not null。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用

注意事项：

1. 非空约束要求每个行都必须包含非空值。
2. 非空约束可以涵盖多个列，但通常只有少数列需要被定义为非空。
3. 定义非空约束时应该选择一个短、有意义且易于记忆的列名。
4. 非空约束列应该是简单的数据类型，如整数或字符串。
5. 在插入数据时，应该确保非空约束列包含非空值，否则会触发非空约束错误。
6. 在更新数据时，应该确保非空约束列包含非空值，否则会触发非空约束错误。
7. 在删除数据时，应该避免删除包含非空约束列的行，否则会触发非空约束错误。
8. 非空约束可以与其他约束一起使用，如主键约束或唯一约束。
9. 在使用非空约束时，应该避免使用复杂的查询和连接操作，以提高查询效率。
10. 非空约束可以在表创建时定义，也可以在表创建后通过 ALTER TABLE 语句添加。

3.4. 外键约束（Foreign Key Constraint）：

用于建立表与表之间的关联关系，保证表之间的关系完整。会用于强制参照完整性，提供单个字段或者多个字段的参照完整性，通常会将外键定义为一个表中的列，该列引用另一个表中的主键列。其中，包含外部键的表称为从表(参照表)，包含外部键所引用的主键或唯一键的表称主表(被参照表)。

外部键约束 foreign key约束指定某一个列或一组列作为外部键，系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。

注意事项：

1.外键约束必须引用目标表的主键或唯一键。
2.外键约束在创建时必须指定ON DELETE和ON UPDATE选项。
3.外键约束会影响表的性能，因此应该谨慎使用。
4.在插入数据时，必须先插入主表的数据，然后再插入从表的数据。
5.在更新或删除主表的数据时，必须先更新或删除从表的数据。
6.如果需要删除主表的记录，必须先删除从表的记录，否则会出现违反外键约束的错误。
7.在使用外键约束时，应该注意表之间的关系，避免出现循环引用的情况。
8.在使用外键约束时，应该考虑到数据的完整性和一致性，避免出现数据冗余或不一致的情况。

3.5. 检查约束（Check Constraint）：

用于限制某个列的取值范围，可以使用SQL表达式来定义检查约束。检查约束可以确保表中某个列的值符合特定的条件。比如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。当执行insert语句或者update语句时，检查约束将验证数据。

注意事项：

1.检查约束是对表中某个列的限制条件，只有满足该条件的数据才能被插入或更新。
2.检查约束可以使用SQL表达式来定义，例如：CHECK (salary > 0)。
3.检查约束可以应用于单个列或多个列，可以在创建表时或后期添加。
4.检查约束会影响表的性能，因此应该谨慎使用。
5.在使用检查约束时，应该考虑到数据的完整性和一致性，避免出现数据冗余或不一致的情况。
6.在插入或更新数据时，必须满足检查约束的条件，否则会出现违反检查约束的错误。
7.在删除检查约束时，需要使用ALTER TABLE语句，例如：ALTER TABLE table_name DROP CONSTRAINT constraint_name。
8.在使用检查约束时，应该注意表之间的关系，避免出现循环引用的情况。

3.6.禁用约束（Disable Constraint）：

用于禁用某个约束，通常用于临时关闭约束以提高性能或进行数据维护。禁用约束可以使用ALTER TABLE语句来实现。具体步骤如下：

1. 使用ALTER TABLE语句，将要禁用约束的表的名称作为参数。
2. 在表名后面添加DISABLE CONSTRAINT子句，指定要禁用的约束名称。
3. 执行该ALTER TABLE语句，即可禁用指定的约束。

例如，禁用名为"emp_dept_fk"的外键约束，可以使用以下SQL语句：

ALTER TABLE emp
DISABLE CONSTRAINT emp_dept_fk;

在禁用约束后，可以对表进行修改操作，如果修改违反了约束条件，则会导致错误。禁用约束后，可以使用ENABLE CONSTRAINT子句重新启用约束（下面有描述）。

注意事项：

1.禁用约束是指暂时停用约束的检查，可以在需要时启用。
2.禁用约束可以使用ALTER TABLE语句来实现，例如：ALTER TABLE table_name DISABLE CONSTRAINT constraint_name。
3.禁用约束会影响表的性能，因此应该谨慎使用。
4.在禁用约束时，需要考虑到数据的完整性和一致性，避免出现数据冗余或不一致的情况。
5.在禁用约束时，应该注意表之间的关系，避免出现循环引用的情况。
6.禁用约束后，可以进行插入、更新和删除操作，但需要自行保证数据的完整性和一致性。
7.在启用约束前，需要先修复数据的错误，否则会出现启用失败的情况。
8.在启用约束时，应该考虑到表之间的关系，避免出现循环引用的情况。
除此之外，还有以下两种约束：

3.7.默认约束（Default Constraint）：

用于设置表中某个列的默认值，当插入数据时，如果没有指定该列的值，则默认使用该约束定义的值。默认值可以算做一个约束，因为语法和创建约束差不多。默认值约束并不会限制什么，当没有给他值的时候，就会默认给你一个值。

3.8.条件约束（Conditional Constraint）：

用于在特定条件下启用或禁用约束，可以使用条件约束来限制数据的插入、更新和删除操作，只有符合指定条件的数据才能被执行操作。常见的条件约束有CHECK约束和WHERE子句约束。

3.8.1. CHECK约束

CHECK约束是通过定义一个条件表达式来限制数据的插入、更新和删除操作，只有符合该条件表达式的数据才能被执行操作。例如，我们可以通过以下语句来创建一个CHECK约束：

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

其中，table_name为表名，constraint_name为约束名，condition为条件表达式。例如，我们可以通过以下语句来创建一个CHECK约束，限制age列的取值范围：

ALTER TABLE student ADD CONSTRAINT check_age CHECK (age >= 18 AND age <= 60);

3.8.2. WHERE子句约束

WHERE子句约束是通过在UPDATE和DELETE语句中使用WHERE子句来限制数据的更新和删除操作，只有符合该WHERE子句条件的数据才能被执行操作。例如，我们可以通过以下语句来更新age列的值，只更新符合条件的数据：

UPDATE student SET age = 20 WHERE age < 18;

注意事项：

在使用条件约束时，需要注意以下几点：

1. CHECK约束条件表达式必须是有效的SQL表达式，否则会导致创建约束失败。
2. CHECK约束条件表达式中可以使用SQL函数和运算符，但要注意函数和运算符的优先级和使用方式。
3. CHECK约束条件表达式中可以引用表中的其他列和表达式，但要注意引用的列和表达式必须存在且已定义。
4. WHERE子句约束只能在UPDATE和DELETE语句中使用，不能在INSERT语句中使用。
5. WHERE子句约束中的条件表达式必须是有效的SQL表达式，否则会导致更新或删除失败。
6. WHERE子句约束中的条件表达式中可以使用SQL函数和运算符，但要注意函数和运算符的优先级和使用方式。
7. WHERE子句约束中的条件表达式中可以引用表中的其他列和表达式，但要注意引用的列和表达式必须存在且已定义。

使用条件约束需要注意表达式的正确性和有效性，以及约束的适用场景和使用方式。在实际应用中，应根据具体情况选择合适的条件约束方式，以保证数据的完整性和正确性。

注意：

以上约束都是在列级别上定义的，也可以在表级别上定义，此时该约束将应用到表中的所有列。

3.9应用到表中的所有列约束

有两种约束可以应用到表中的所有列，它们分别是：

3.9.1. 表级别的唯一约束（Unique Constraint）：

用于保证表中所有列的值都是唯一的，但允许空值存在。这种约束可以在CREATE TABLE或ALTER TABLE语句中定义，如下所示：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2, ...));
```

```
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

3.9.2. 表级别的检查约束（Check Constraint）：

用于限制表中所有列的取值范围或满足一定的条件。这种约束也可以在CREATE TABLE或ALTER TABLE语句中定义，如下所示：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name CHECK (condition));
```

```
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

注意：

表级别的约束会影响表中所有列的数据，应该谨慎使用，尽量在列级别上定义约束。

四、约束语法

4.1创建约束：

4.1.1非列模式,不带约束名

语法：

create table 表名(字段名1 字段类型 约束,
          字段名2 字段类型 约束,
          字段名3 字段类型 约束,
          ...
         )

比如：

create table SS (fw_id   number         primary key,
                 fw_name varchar2(20)   not null,
                 fw_sex  varchar2(6)    check(fw_sex in('男','女','张三')),
                 fw_sfzh varchar2(20)   unique not null,
                 fw_date date           default date'2022-1-1');

select * from SS;

注意：

检查约束检查的列是它约束的列，
它约束的列的字段类型要和条件的字段类型一致，
比如被约束的字段类型为varchar2，
那它的条件就不能为between 1 and 100，
一个列可以添加多个约束，not null写在最后。

4.1.2非列模式，带约束名

当不给添加约束名的时候系统会自动给约束约束名，我们也可以手动添加约束名。

语法：

create table 表名(字段名1 字段类型 constraint 约束名 约束,
                  字段名2 字段类型 constraint 约束名 约束,
                  字段名3 字段类型 constraint 约束名 约束,
                   ...)

比如：

create table stu_2(
 stu_id     number(4)      constraint  pk_nana  primary key
,stu_name   varchar2(4000) constraint  nn_lala  not null
,stu_sex    varchar2(6)    constraint  ck_lulu  check(stu_sex in('男','女','其他'))
,stu_idcard char(18)       constraint  un_lili  unique constraint nn_kiki not null  --一个列 
          可以加多个约束,not null写在最后
,stu_bith   date           default date'2000-1-1' --默认值不能取名字
);

4.1.3列模式，不带约束名

语法:

create table 表名(字段名1 字段类型,
                  字段名2 字段类型,
                  字段名3 字段类型,
                  ...
                  约束(字段1),
                  约束(字段2),
                  约束(字段3),
                   ...)

比如：

create table stu_3(
 stu_id     number(4)      
,stu_name   varchar2(4000) 
,stu_sex    varchar2(6)    
,stu_idcard char(18)       
,stu_bith   date     default date'2000-1-1'
,primary key(stu_id)
,check(stu_name is not null)    
,check(stu_sex in('男','女'))
,unique(stu_idcard)
,check(stu_idcard is not null)
);

注意：

默认值不能用列模式，非空用列模式要转成检查

4.1.4列模式，带约束名

语法：

create table 表名(字段名1 字段类型,
                  字段名2 字段类型,
                  字段名3 字段类型,
                  ...
                  constraint 约束名 约束(字段1),
                  constraint 约束名 约束(字段2),
                  constraint 约束名 约束(字段3),
                  ...)

区别是在表创好了后在最后的地方增加约束。

比如：

create table stu_4(
 stu_id     number(4)      
,stu_name   varchar2(4000) 
,stu_sex    varchar2(6)    
,stu_idcard char(18)       
,stu_bith   date           default date'2000-1-1'
,constraint pk_1 primary key(stu_id)
,constraint nn_1 check(stu_name is not null)
,constraint ck_1 check(stu_sex in('男','女'))
,constraint un_1 unique(stu_idcard)
,constraint nn_2 check(stu_idcard is not null)
);

4.2复合主键

复合主键（组合主键），多个列合在一起充当主键，它们一起唯一标识一行数据，起到唯一且非空的限制作用。在使用复合主键时，可以使用约束来确保数据完整性和一致性。

语法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...);

比如：假设有一个名为“employee”的表格，其中包含员工的姓名（name）、工号（employee_id）和部门编号（department_id）。使用以下语句来创建复合主键约束：

ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id, department_id);

这将创建一个名为“pk_employee”的主键约束，它由“employee_id”和“department_id”两列组成。这个约束确保了每个员工在同一部门中拥有唯一的工号。

当我们试图插入一个具有相同工号和部门编号的员工时，Oracle会抛出一个错误，因为这会违反主键约束：

INSERT INTO employee (name, employee_id, department_id)
VALUES ('John Smith', 1001, 1);

-- Throws error: ORA-00001: unique constraint (SCHEMA.PK_EMPLOYEE) violated

因此，使用复合主键约束可以确保数据的完整性和一致性，避免重复数据的出现，提高数据质量。

比如：将学生编号和学生姓名创建复合主键

create table stu_4(
 stu_id     number(4)      
,stu_name   varchar2(4000) 
,stu_sex    varchar2(6)    
,stu_idcard char(18)       
,stu_bith   date           default date'2000-1-1'
,constraint pk_1 primary key(stu_id,stu_name)
,constraint nn_1 check(stu_name is not null)
,constraint ck_1 check(stu_sex in('男','女'))
,constraint un_1 unique(stu_idcard)
,constraint nn_2 check(stu_idcard is not null)
);

注意：

外键约束和复合主键都是只能用列模式创建！！！

4.3创建外键约束

语法:

[constraint 外键约束名] foreign key(外键列) references 父表(主键列) 
[on delete cascade|on delete set null]    --foreign 外部的,references 参照

比如：在emp010表中添加一个名为fk1的外键约束

alter table emp010 
add constraint fk1 foreign key(deptno) refereces dept(deptno) --增加一个外键约束

该约束将deptno列作为外键，参考dept表的deptno列作为主键。将emp010表的deptno列与dept表的deptno列关联起来，确保emp010表中的deptno列只能包含在dept表中存在的deptno值，从而保证数据的完整性和一致性。

例题：创建emp1 包含三个列，empno,ename,deptno(参照emp表)，它的deptno上创建外键约束,参考dept的deptno，语句如下：

create table emp1(
empno number
,ename varchar2(20)
,deptno number(2)
,constraint fk_1 foreign key(deptno) references dept(deptno) on delete set null);

注意：

外键列是可以为空，也可以不唯一；
在删除父表的数据时，

如果不指定级联删除，必须先删除子表的相关项，再删除父表的对应行；
如果指定了级联删除，子表的相关项和父表的对应行一起被删除；
如果指定了级联删除为空，父表的对应行被删除，子表的相关项还在，外键列的值变成空 deptno列变成空。

插入/更新到子表的外键列的数据必须是父表主键列里存在的；
插入到父表主键列的数据没有限制(只需要符合主键约束) ；

级联删除 on delete cascade
级联删除为空 on delete set null
不指定级联删除 on delete no action --默认

4.4增加约束

语法：

alter table 表名 add[constraint 约束名]约束类型(列);

比如：

alter table dept1 add primary key(deptno)

4.5约束重命名

alter table 表名 rename constraint 旧名字 to 新名字

比如：

alter table dept1 rename constraint SYS_C0011932 to qwer;
alter table dept1 rename constraint "qw" to "123";

4.6约束的启用和禁用

当要插入大量的数据时，约束都会去检查是否符合，消耗资源浪费时间。

语法：

ALTER TABLE 表名 DISABLE|ENABLE [VALIDATE|NOVALIDATE] CONSTRAINT 约束名;

4.7约束的四种状态

ENABLE VALIDATE : 正常地启用约束（新数据和旧数据都要符合约束）
ENABLE NOVALIDATE : 不正常地启用约束（表中可以有不合约束的状态,新数据必须符合约束，只对检查约束可以用）
DISABLE NOVALIDATE : 正常地禁用约束（约束失效,可以输入不符合约束的值）
DISABLE VALIDATE : 锁表（不能对全表进行插入、更新、删除）

4.8修改非空、默认值、列的类型长度

语法：

alter table 表名 modify 列名 类型长度 [[default 值][not null|null]]

注意：

改为非空列

·要修改列的类型，该列必须为空；
·要把列修改成可为空，该列必须现在是非空，反之亦然；
·varchar2的长度可以改长,如果要改短,必须不短于该列已有数据的最长长度；
·number要修改长度,必须此列为空；

4.9删除约束

语法：

alter table 表名 drop constraint 约束名;

举例运用：

给emp100的empno列增加一个主键约束

create table emp100 as select * from emp;
alter table emp100 add primary key(empno);

给emp100的ename列增加一个唯一约束，并起名为un100

alter table emp100 add constraint un100 unique (ename);

删除上问增加的唯一约束

alter table emp100 drop constraint un100;

将hiredate列修改为不可为空

alter table emp100  modify hiredate not null;

给deptno列设置默认值为10

alter table emp100 modify deptno default 10;

树贤森

关注

4
点赞
踩
12

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle中的约束

Oracle中的约束
复制链接

扫一扫

专栏目录

Oracle中的约束

一、认识约束

二、约束作用

三、约束分类

3.1. 主键约束（Primary Key Constraint）：

注意事项：

3.2. 唯一约束（Unique Constraint）：

注意事项：

3.3. 非空约束（Not Null Constraint）：

注意事项：

3.4. 外键约束（Foreign Key Constraint）：

注意事项：

3.5. 检查约束（Check Constraint）：

注意事项：

3.6.禁用约束（Disable Constraint）：

注意事项：

3.7.默认约束（Default Constraint）：

3.8.条件约束（Conditional Constraint）：

3.8.1. CHECK约束

3.8.2. WHERE子句约束

注意事项：

3.9应用到表中的所有列约束

3.9.1. 表级别的唯一约束（Unique Constraint）：

3.9.2. 表级别的检查约束（Check Constraint）：

四、约束语法

4.1创建约束：

4.1.1非列模式,不带约束名

4.1.2非列模式，带约束名

4.1.3列模式，不带约束名

4.1.4列模式，带约束名

4.2复合主键

4.3创建外键约束

4.4增加约束

4.5约束重命名

4.6约束的启用和禁用

4.7约束的四种状态

4.8修改非空、默认值、列的类型长度

4.9删除约束

“相关推荐”对你有帮助么？