1. 约束语法
1. 列级定义：只能引用一个列，但表中可以有多个列级定义
2. 表级定义：引用一个或多个列 ，其中Not Null 没有表级定义
3. 追加定义：建表后，用 ALTER TABLE命令追加约束
2. 约束分类
1. Primary Key：主键约束----非空且唯一，创建主键时，若主键列上已经有索引，则主键直接引用该索引，且在主键删除时，该索引仍然存在；若主键列上没有索引，则创建主键时，会自动创建唯一索引，且主键删除后，该索引也会被删除
2. Foreign Key：外键约束----允许有重复值，允许值为空，定义时必须引用主键
3. Not Null：非空约束----允许值重复
4. Check：检查约束-----对插入的数据进行检查
5. Unique：唯一约束----允许值为空，针对的是非空值
1 SQL> create table u_test (id number unique,name varchar2(10));-----列级定义
2
3 Table created.
4
5 SQL> insert into u_test values(1,'a');
6
7 1 row created.
8
9 SQL> insert into u_test values(1,'a');
10 insert into u_test values(1,'a')
11 *
12 ERROR at line 1:
13 ORA-00001: unique constraint (SCOTT.SYS_C009221) violated
14
15 SQL> create table u_test (id number,name varchar2(10),constraint unique_test unique(id));-----表级定义
16
17 Table created.
18
19 SQL> insert into u_test values(1,'a');
20
21 1 row created.
22
23 SQL> insert into u_test values(1,'a');
24 insert into u_test values(1,'a')
25 *
26 ERROR at line 1:
27 ORA-00001: unique constraint (SCOTT.UNIQUE_TEST) violated
28
29 SQL> create table u_test (id number,name varchar2(10));
30
31 Table created.
32
33 SQL> alter table u_test add constraint unique_test unique(id);-----追加定义
34
35 Table altered.
36
37 SQL> insert into u_test values(1,'a');
38
39 1 row created.
40
41 SQL> insert into u_test values(1,'a');
42 insert into u_test values(1,'a')
43 *
44 ERROR at line 1:
45 ORA-00001: unique constraint (SCOTT.UNIQUE_TEST) violated
3. 主外键实验
1 SQL> conn scott/tiger@erp
2 Connected.
--------------------创建两张新表--------------------
3 SQL> create table testemp as select * from emp;
4
5 Table created.
6
7 SQL> create table testdept as select * from dept;
8
9 Table created.
10
-----------------------通过追加定义，给两张新表分别追加主键约束、外键约束----------------------------
11 SQL> alter table testdept add constraint pk_d_testdept primary key (deptno);
12
13 Table altered.
14
15 SQL> alter table testemp add constraint fk_e_testemp foreign key(deptno) references testdept(deptno);
16
17 Table altered.
18
-----------------------查看表上的约束及索引-------------------------------
19 SQL> select TABLE_NAME,CONSTRAINT_NAME ,index_name from user_constraints;
20
21 TABLE_NAME CONSTRAINT_NAME INDEX_NAME
22 --------------- --------------- ---------------
23 DEPT PK_DEPT PK_DEPT
24 EMP PK_EMP PK_EMP
25 EMP FK_DEPTNO
26 TESTDEPT PK_D_TESTDEPT PK_D_TESTDEPT（该索引是创建主键时，自动创建的唯一索引）
27 TESTEMP FK_E_TESTEMP
28
------------------删除主键表（父表）中的数据时，会有报错，因为有外键约束----------------------------
29 SQL> delete testdept where deptno=20;
30 delete testdept where deptno=20
31 *
32 ERROR at line 1:
33 ORA-02292: integrity constraint (SCOTT.FK_E_TESTEMP) violated - child record found
34
35
36 SQL> delete testdept;
37 delete testdept
38 *
39 ERROR at line 1:
40 ORA-02292: integrity constraint (SCOTT.FK_E_TESTEMP) violated - child record found
41
-------------可以正常删除外键表（子表）中的数据----------------------
43 SQL> delete testemp where deptno=20;
44
45 5 rows deleted.
46
47 SQL> rollback;
48
49 Rollback complete.
50
------------在外键表（子表）中插入父表中不存在的数据时，也会有报错，因为有主键约束-------------------
51 SQL> insert into testemp(empno,deptno) values(9999,60);--- 60 在父表中不存在
52 insert into testemp(empno,deptno) values(9999,60)
53 *
54 ERROR at line 1:
55 ORA-02291: integrity constraint (SCOTT.FK_E_TESTEMP) violated - parent key not found
56
-------------------先删除外键约束，再删除主键约束------------------------
57 SQL> alter table testdept drop constraint pk_d_testdept;
58 alter table testdept drop constraint pk_d_testdept
59 *
60 ERROR at line 1:
61 ORA-02273: this unique/primary key is referenced by some foreign keys
62
63
64 SQL> alter table testemp drop constraint fk_e_testemp;
65
66 Table altered.
67
68 SQL> alter table testdept drop constraint pk_d_testdept;
69
70 Table altered.
71
-------------------主键约束被删除后，自动创建的唯一索引也会被删除---------------------
72 SQL> select TABLE_NAME,CONSTRAINT_NAME ,index_name from user_constraints;
73
74 TABLE_NAME CONSTRAINT_NAME INDEX_NAME
75 --------------- --------------- ---------------
76 DEPT PK_DEPT PK_DEPT
77 EMP PK_EMP PK_EMP
78 EMP FK_DEPTNO