LLL的数据库培训-79-第五部分—Oracle11g-Oracle SQL对象管理—第6讲—约束
一、约束的概念
1、约束是什么
在 Oracle 数据库中，约束是用于保持数据完整性和一致性的规则或条件。
它们用于限制表中数据的值，确保数据满足特定的要求。
以下是 Oracle 数据库中常见的约束概念：
（1）主键约束（Primary Key Constraint）：
主键约束用于定义列或列组合作为表的主键。主键值必须是唯一的且不为空，确保每行的唯一性标识。
（2）唯一约束（Unique Constraint）：
唯一约束用于确保指定列或列组合中的值是唯一的，不允许重复。唯一约束可以包含空值，但是不同行之间的值必须唯一。
（3）外键约束（Foreign Key Constraint）：
外键约束用于定义表之间的关系。它确保一个表中的列值在另一个表的主键或唯一约束中存在。
（4）检查约束（Check Constraint）：
检查约束用于定义列或列组合的条件，这些条件必须满足才能插入或更新数据。它基于特定的布尔表达式或函数来验证数据的有效性。
（5）非空约束（Not Null Constraint）：
非空约束用于确保指定列不允许包含空值。这意味着在插入或更新数据时，这些列必须具有非空值。
这些约束可以在创建表时定义，也可以在表已经存在时进行修改或添加。
它们不仅可以确保数据完整性，还可以提供其他查询优化和数据保护功能。使用约束可以增强数据库的稳定性和可靠性，并帮助开发人员避免数据错误和不一致。
二、主键约束
1、主键约束介绍
在 Oracle 数据库中，主键约束（Primary Key Constraint）用于定义一个或多个列作为表的主键。主键是用于唯一标识表中每一行数据的列或列组合。以下是关于 Oracle 主键约束的详细解释：
（1）定义主键约束：
主键约束可以在创建表时一起定义，也可以在表已经存在时进行修改或添加。在创建表时，可以使用 PRIMARY KEY 关键字后跟要定义为主键的列名。
示例：CREATE TABLE my_table (id NUMBER PRIMARY KEY, name VARCHAR2(50));
（2）唯一性：
主键值必须是唯一的，也就是说，每一行的主键值都必须与其他行不同。这样可以确保在表中查找、更新或删除数据时，可以准确定位到特定行。
唯一性也要求主键值不包含 NULL 值。
（3）非空性：
主键列不允许包含空值（NULL）。主键列的值必须在插入或更新行时提供，否则将触发错误。
（4）主键索引：
为了支持主键约束的快速查询和数据访问，Oracle 会自动为主键列创建一个索引。这些索引有助于提高主键列的检索性能。
（5）引用完整性：
主键约束还可以用于定义外键引用完整性。其他表可以使用主键列作为外键参照，确保关系数据库实现的数据一致性。
（6）主键的修改和删除：
在表已经存在的情况下，可以使用 ALTER TABLE 语句修改或删除主键约束。修改主键约束可能需要先删除原有的主键约束，然后重新创建新的约束。
总之，主键约束是用于确保表中数据唯一性和完整性的关键机制。通过定义主键，可以方便地标识和访问表中的特定行，以及建立与其他表的关联。同时，主键约束还通过自动创建主键索引来提高查询性能。
2、创建主键约束
（1）创建表时，在定义列内直接创建主键约束
create table test01( id number primary key, name varchar2(10));
此时自动生成一个约束名称如：SYS_C0014880，同时会生成同样名称的索引。
col owner for a10 col table_name for a15 col index_name for a15 col constraint_name for a15 select owner,table_name,constraint_name,constraint_type,index_name from user_constraints;
（2）创建表时，在定义列内直接创建主键约束，并定义约束名称
create table test02( id number constraint id_pk primary key using index tablespace users, name varchar2(10)); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST02';
（3）创建时，在定义列外单独设置主键约束
优点，可以在多个列上定义约束
create table test03( id number, name varchar2(10), sex varchar2(3), constraint test03_id_pk primary key(id) using index tablespace users, constraint test03_ns_u unique(name,sex) );
select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST03';
（4）创建表后，在定义表外单独创建主键约束
启用主键约束后，Oracle会自动创建与该主键约束关联的唯一索引。
create table test04(id number,name varchar2(10)); alter table test04 add constraint test04_id_pk primary key(id); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST04';
（5）创建表后，在定义表外，可以先创建约束再创建主键
此方法可以在不影响索引的情况下删除或禁用主键约束。
如果不是在创建主键前创建索引，那么不论何时删除或禁用主键，约束、索引都会被自动删除。
create table test05(id number,name varchar2(10)); create index ind_test05_id on test05(id) tablespace users; alter table test05 add constraint test5_id_pk primary key(id); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST05';
3、主键约束查看
select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST04';
4、违反约束测试
例：
15:26:57 TEST@mesorcl>insert into test02 values(1,'LLL01');
15:27:06 TEST@mesorcl>insert into test02 values(2,'LLL02');
15:27:11 TEST@mesorcl>commit;
15:27:13 TEST@mesorcl>insert into test02 values(2,'LLL03');
insert into test02 values(2,'LLL03')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.ID_PK) violated
15:27:20 TEST@mesorcl>insert into test02 values(3,'LLL02');
15:27:38 TEST@mesorcl>insert into test02 values(null,'LLL04');
insert into test02 values(null,'LLL04')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TEST02"."ID")
15:27:49 TEST@mesorcl>insert into test02 values(4,null);
15:28:07 TEST@mesorcl>select * from test02;
ID NAME
---------- -----------
1 LLL01
2 LLL02
3 LLL02
4
三、唯一约束
1、唯一约束概念
在 Oracle 数据库中，唯一约束（Unique Constraint）用于确保指定列或列组合中的值是唯一的，不允许重复。
唯一约束可以在创建表时定义，也可以在已存在的表上进行修改或添加。
和主键一样，Oracle会自动创建与唯一键约束有关的索引。
（1） 定义唯一约束：
在创建表时，可以使用 `UNIQUE` 关键字后跟要定义为唯一约束的列名。
示例：
`CREATE TABLE my_table (id NUMBER, name VARCHAR2(50), CONSTRAINT uk_name UNIQUE (name));`
（2）唯一性：
唯一约束要求列或列组合中的值是唯一的，除了 NULL 值以外。
这意味着在表中任何时候都不允许存在重复的值。
（3）关于空值：
唯一约束允许空值（NULL），但是不同行之间的值必须唯一。
也就是说，如果一个列被定义为唯一约束并且允许 NULL 值，那么在多行数据中，可以有多个 NULL 值，但其他非空值必须是唯一的。
（4）多列唯一约束：
唯一约束可以同时应用于多个列，称为多列唯一约束。这种约束确保多个列中所有值的组合是唯一的。
（5）主键与唯一约束的区别：
主键约束（Primary Key Constraint）是唯一约束的一种特殊形式，它不允许空值，并且在表中只允许定义一个主键。
与主键不同，唯一约束允许空值，并且可以在表中定义多个唯一约束。
（6）修改和删除唯一约束：
如果需要修改已存在的唯一约束，可以使用 `ALTER TABLE` 语句进行修改。
要删除唯一约束，可以使用 ALTER TABLE ... DROP CONSTRAINT 语句。
（7）总结
唯一约束用于确保数据的唯一性，可以通过避免重复值来维护数据的完整性。
唯一约束还为数据库提供了更好的查询性能和数据一致性。在设计数据库表结构时，根据具体需求，合理使用唯一约束可以提高数据质量和查询效率。
2、案例
（1）创建表时，在定义列内创建唯一约束（约束名称有系统定义）
create table test06( id number, name varchar2(10) unique); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST06';
（2）创建表时，在定义列内创建唯一约束（约束名称自定义）
create table test07( id number, name varchar2(10) constraint test07_name_uk1 unique); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST07';
（3）创建表时，在定义列外创建唯一索引（约束名称自定义，指定创建的索引指定表空间）
create table test08( id number, name varchar2(10) constraint test08_name_uk1 unique using index tablespace users); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST08';
（4）创建表后，在定义表外单独创建唯一索引（约束名称自定义，指定创建的索引指定表空间）
create table test09( id number, name varchar2(10)); alter table test09 add constraint test09_id_uk1 unique(id) using index tablespace users; select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST09';
（5）创建表后，在定义表外单独创建唯一索引（先创建索引，再创建约束）
create table test10( id number, name varchar2(10)); create index ind_test10_id_uk1 on test10(id) tablespace users; alter table test10 add constraint ind_test09_id_uk1 unique(id); select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST10';
3、查看约束
select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST10';
4、违反约束测试
15:38:52 TEST@mesorcl>insert into test10 values(1,'LLL01'); 15:39:01 TEST@mesorcl>insert into test10 values(1,'LLL01'); insert into test10 values(1,'LLL01') * ERROR at line 1: ORA-00001: unique constraint (TEST.IND_TEST09_ID_UK1) violated 15:39:04 TEST@mesorcl>insert into test10 values(2,'LLL02'); 15:39:10 TEST@mesorcl>insert into test10 values(null,'LLL03'); 15:39:16 TEST@mesorcl>insert into test10 values(null,'LLL04');
四、外键约束
1、外键约束概念
（1）概念
在 Oracle 数据库中，外键约束（Foreign Key Constraint）用于定义表之间的关系。
它确保一个表中的列值在另一个表的主键或唯一约束中存在。
外键约束允许在一个表中创建对另一个表的引用，实现数据库的关联性和数据完整性。
使用外键约束可以确保列中的值不超出某个值域。
外键定义用在子表中。
外键约束必须引用父表中定义了的主键或唯一键的列。
（2）定义外键约束：
在创建或修改表时，可以使用 REFERENCES 子句定义外键约束。
示例：
CREATE TABLE orders ( order_id NUMBER, customer_id NUMBER, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers (customer_id) );
（3）关联表的主键或唯一约束：
外键约束依赖于关联表的主键或唯一约束。
在示例中，orders 表中的 customer_id 列是外键，它引用了 customers 表中的 customer_id 主键列。
（4）引用动作：
外键约束定义了当关联表的主键或唯一约束的值发生更改或删除时，应采取的动作。
常见的引用动作包括：
CASCADE： 级联，表示当主键或唯一约束的值发生变化时，外键所引用的列也会相应更新。 SET NULL： 设置为空，表示当主键或唯一约束的值发生变化时，外键所引用的列会被设置为空值。 SET DEFAULT： 设置为默认值，表示当主键或唯一约束的值发生变化时，外键所引用的列会被设置为默认值。 RESTRICT： 限制，表示当主键或唯一约束的值发生变化时，如果存在外键引用关系，则不允许进行操作。 NO ACTION： 不执行任何操作。
（5）外键约束的命名：
可以为外键约束指定一个名称，以便更好地标识和管理约束。如果不指定名称，Oracle 数据库会自动为外键约束生成一个默认名称。
（6）修改和删除外键约束：
要修改已存在的外键约束，可以使用 ALTER TABLE 语句进行修改。
要删除外键约束，可以使用 ALTER TABLE ... DROP CONSTRAINT 语句。
（7）总结：
通过使用外键约束，可以确保关联表之间的数据完整性和一致性。
外键约束可以在数据库层面实施引用完整性，并通过阻止插入或更新不符合关联条件的数据来保护数据质量。
在设计数据库表结构时，合理使用外键约束可以建立起表之间的关系，并提供更强大的数据管理能力。
2、案例
首先要创建父表：
create table dept( dept_id number primary key, dept_desc varchar2(10));
（1）创建子表时，在定义列内直接创建外键约束
create table emp01( emp_id number, name varchar2(20), dept_id constraint emp01_detpt_fk references dept(dept_id));
注意：子表emp01中的dept_id列并没有定义数据类型，外键约束会从引用的表dept的dept_id列获取数据类型。
（2）创建子表时，在定义列之内设置外键定义（可以直接定义数据类型）
create table emp02( emp_id number, name varchar2(20), dept_id number constraint emp02_detpt_fk references dept(dept_id));
（3）创建子表时，在定义列之外设置外键定义
create table emp03( emp_id number, name varchar2(20), dept_id number, constraint emp03_dept_fk foreign key(dept_id) references dept(dept_id));
（4）创建子表后，在定义表之外设置外键定义
create table emp04( emp_id number, name varchar2(20), dept_id number); alter table emp04 add constraint emp04_dept_fk foreign key (dept_id) references dept(dept_id);
3、查看约束
select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST10';
4、违反约束测试
例：
5、注意：
与主键和唯一键约束不同，Oracle不会自动为外键列添加索引；必须手动为它们创建索引。
五、check约束（检查约束）
1、check约束概念
（1）概念
在 Oracle 数据库中，CHECK 约束用于限制表中数据列的取值范围。
CHECK 约束定义了一个条件，只有满足该条件的行才能被插入或更新到表中。
（2）使用情况
如果限定值域（如限定列只能含有Y或N值）较小时，使用CHECK约束效果更好。
如果限定值域较大，并且需要定时更新，那么使用外键约束效果更好。
（3） 定义 CHECK 约束：
在创建表时或修改已存在的表时，可以使用 CHECK 条件来定义 CHECK 约束。示例：`CREATE TABLE employees (employee_id NUMBER, salary NUMBER CHECK (salary > 0));`
（4）CHECK 条件：
CHECK 约束使用一个逻辑表达式来定义允许的数据取值范围。这个表达式可以是简单的比较运算，也可以是复杂的逻辑组合。在示例中，CHECK 约束确保 `salary` 列的值大于 0。
（5）多列 CHECK 约束：
CHECK 约束可以同时应用于多个列，以实现多列数据的一致性约束。这些列可以使用 AND 或 OR 运算符来组合成一个复杂的条件。
（6）CHECK 约束和域值约束：
在 Oracle 中，CHECK 约束可以用于检查列值的有效性，类似于域值约束。然而，CHECK 约束具有更大的灵活性，因为它可以使用更复杂的逻辑条件，并且可以应用于整个表或表的特定列。
（7）修改和删除 CHECK 约束：
要修改已存在的 CHECK 约束，可以使用 ALTER TABLE 语句进行修改。要删除 CHECK 约束，可以使用 ALTER TABLE ... DROP CONSTRAINT 语句。
（8）总结
CHECK 约束提供了对表中数据取值范围的验证机制，可以保证数据的有效性和一致性。
通过使用 CHECK 约束，可以在数据库层面实施数据完整性约束，避免插入或更新不符合条件的数据。
在设计数据库表结构时，合理使用 CHECK 约束可以提高数据质量，并提供更强大的数据管理和保护能力。
2、案例
（1）创建表时，在定义列内定义check约束
create table test11(
id number,
name varchar(20),
sex varchar2(3) check (sex in('M','F')));
create table test111( id number,
name varchar(20),
dept_id number check (dept_id<10));
（2）创建表时，在定义列内定义check约束（定义check约束名称）
create table test12(
id number,
name varchar(20),
sex varchar2(3) constraint test12_sex_chk check (sex in('M','F')));
（3）创建表时，在定义列外定义check约束（定义check约束名称）
create table test13(
id number,
name varchar(20),
sex varchar2(3) ,
constraint test13_sex_chk check (sex in('M','F')));
（4）创建表后，在定义表外定义check约束
create table test14( id number, name varchar(20), sex varchar2(3));
alter table test14 add constraint test14_sex_chk check(sex in('M','F'));
3、查看约束
select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST11';
4、违反约束测试
例:
5、注意：
检查约束必须评估插入到行中的数据或在行中更新的数据，从而得出真或未知（NULL）值。
不能在检查约束中使用子查询或序列。
不能引用SQL函数（UID,USR,SYSDATE和USERENV)和位列（LEVEL和ROWNUM）。
六、not null 约束（非空约束）
1、not null概念
在 Oracle 数据库中，NOT NULL 约束用于确保表中的列不能为空。
NOT NULL 约束要求在插入或更新表时，被约束的列必须包含非 NULL 值。
（1）定义 NOT NULL 约束：
在创建表时或修改已存在的表时，可以在列定义中使用 NOT NULL 关键字来定义 NOT NULL 约束。
（2）约束作用：
NOT NULL 约束用于强制要求列中的值不能为空，即禁止插入或更新为空值（NULL）。
（3）多列 NOT NULL 约束：
NOT NULL 约束可以同时应用于多个列，以确保多个列的值都不能为空。
（4）修改列的 NULL 约束：
可以使用 ALTER TABLE 语句来修改已存在列的 NULL 约束，将其设为 NOT NULL。
（5） 删除 NOT NULL 约束：
如果需要删除已存在的 NOT NULL 约束，可以使用 ALTER TABLE 或 DROP CONSTRAINT 语句进行删除。
（6）总结
NOT NULL 约束用于确保表中的列不允许为空值。
通过在数据库层面强制要求列的非空性，可以确保数据的完整性和质量。
在设计数据库表结构时，合理使用 NOT NULL 约束可以保证有关键性的信息的完整性，并提供更强大的数据管理能力。
2、案例
（1）创建表时，在定义列内定义not null约束
create table test15( id number, name varchar2(10) not null);
（2）创建表时，在定义列内定义not null约束（自定义名称）
create table test16( id number, name varchar2(10) constraint test16_name_nn not null);
（3）创建表后，在定义表外定义not null约束
create table test17( id number, name varchar2(10) ); alter table test17 modify(name not null);
3、查看约束
select owner,table_name,constraint_name,constraint_type,index_name from user_constraints where table_name='TEST11';
4、违反约束测试
例：
5、注意：
如果当前添加了not null约束的列中含有NULL值，必须先更新表使该列的每一行都有值。
七、约束管理
1、禁用约束
alter table test16 disable constraint test16_name_nn; select owner,table_name,constraint_name,constraint_type,index_name,status from user_constraints where table_name='TEST16';
2、启用约束
alter table test16 enable constraint test16_name_nn; select owner,table_name,constraint_name,constraint_type,index_name,status from user_constraints where table_name='TEST16';
3、删除约束
alter table test16 drop constraint test16_name_nn;
4、重命名约束
alter table test08 rename constraint TEST08_NAME_UK1 to TEST08_NAME_UK2;
5、修改约束定义
修改了约束定义，也行改了约束名
删除原约束，再进行新约束修改
select owner,table_name,constraint_name,constraint_type,index_name,status from user_constraints where table_name='TEST111';
alter table test111 drop constraint SYS_C0014925; ALTER TABLE test111 modify dept_id constraint dept_id_ck check(dept_id < 20);
请注意，删除约束的操作将永久性地删除约束，并可能影响表的数据完整性和一致性。在删除约束之前，请确保了解其影响，并备份表中的数据以防止意外数据损失。
另外，删除主键约束时需要注意，因为主键约束还可能是其他表的外键依赖项。在删除主键约束之前，请确保先删除其他表中对该主键的外键约束，并在必要时更新相关数据。
