Oracle的约束_oracle关闭外键约束

---

Oracle的约束

一、简介

• 定义规则
• 确保完整性

二、非空约束

1、在创建表时设置非空约束

• create table 表名(列名 数据类型 not null,…);

# 建表
SQL> create table userinfo_1 (id number(6,0), username varchar2(20) not null, userpwd varchar2(20) not null);

表已创建。

# 查看表中字段
SQL> desc userinfo_1
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(6)
 USERNAME                                  NOT NULL VARCHAR2(20)
 USERPWD                                   NOT NULL VARCHAR2(20)
 
# 插入id，失败
SQL> insert into userinfo_1(id) values(1);
insert into userinfo_1(id) values(1)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYSTEM"."USERINFO_1"."USERNAME")

# 插入数据
SQL> insert into userinfo_1(id,username,userpwd) values(1,'jack','123123');

已创建 1 行。

# 查询表中数据
SQL> select * from userinfo_1;

        ID USERNAME             USERPWD
---------- -------------------- --------------------
         1 jack                 123123
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2、在修改表时添加非空约束

• alter table 表名 modify 列名 数据类型 not null;

# 尝试在修改表时添加非空约束，发现报错，需要把表中数据删除，再添加非空约束
SQL> alter table userinfo modify username varchar2(20) not null;
alter table userinfo modify username varchar2(20) not null
*
第 1 行出现错误:
ORA-02296: 无法启用 (SYSTEM.) - 找到空值

# 删除表中数据
SQL> delete from userinfo;

已删除3行。

# 在修改表时添加非空约束
SQL> alter table userinfo modify username varchar2(20) not null;

表已更改。

# 查询字段
SQL> desc userinfo
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(6)
 USERNAME                                  NOT NULL VARCHAR2(20)
 USERPWD                                            VARCHAR2(20)
 EMAIL                                              VARCHAR2(30)
 REGDATE                                            DATE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3、在修改表时去除非空约束

• alter table 表名 modify 列名 数据类型 null;

SQL> alter table userinfo modify username varchar2(20) null;

表已更改。

SQL> desc userinfo
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(6)
 USERNAME                                           VARCHAR2(20)
 USERPWD                                            VARCHAR2(20)
 EMAIL                                              VARCHAR2(30)
 REGDATE                                            DATE
1
2
3
4
5
6
7
8
9
10
11
12

三、主键约束

1、简介

• 一张表只能设计一个主键约束
• 主键约束可以由多个字段构成（联合主键或复合主键）

2、在创建表时设置主键约束

• create table 表名(列名 数据类型 primary key,…);

SQL> create table userinfo_p (id number(6,0) primary key, username varchar2(20), userpwd varchar2(20));

表已创建。

SQL> desc userinfo_p;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(6)
 USERNAME                                           VARCHAR2(20)
 USERPWD                                            VARCHAR2(20)
1
2
3
4
5
6
7
8
9
10

• create table 表名(列名1 数据类型1,constraint 主键约束名 primary key(列名1,…);

SQL> create table userinfo_p1 (id number(6,0), username varchar2(20), userpwd varchar2(20), constraint pk_id_username primary key(id,username));

表已创建。

SQL> desc userinfo_p1
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(6)
 USERNAME                                  NOT NULL VARCHAR2(20)
 USERPWD                                            VARCHAR2(20)

SQL> desc user_constraints
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

SQL> select constraint_name from user_constraints where table_name='USERINFO_P1';

CONSTRAINT_NAME
------------------------------
PK_ID_USERNAME

SQL> select constraint_name from user_constraints where table_name='USERINFO_P';

CONSTRAINT_NAME
------------------------------
SYS_C0011161
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

3、在修改表时添加主键约束

• alter table 表名 add constraint 主键约束名 primary key(列名1,…);

SQL> alter table userinfo add constraint pk_id primary key(id);

表已更改。

SQL> desc user_constraints
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)
 
SQL> select constraint_name from user_constraints where table_name='USERINFO';

CONSTRAINT_NAME
------------------------------
PK_ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

4、更改主键约束的名称

• alter table 表名 rename constraint 旧主键名 to 新主键名

SQL> alter table userinfo rename constraint pk_id to new_pk_id;

表已更改。

SQL> select constraint_name from user_constraints where table_name='USERINFO';

CONSTRAINT_NAME
------------------------------
NEW_PK_ID
1
2
3
4
5
6
7
8
9

5、禁用主键约束

• alter table 表名 disable|enable constraint 主键名

SQL> alter table userinfo disable constraint new_pk_id;

表已更改。

SQL> select constraint_name,status from user_constraints where table_name='USERINFO';

CONSTRAINT_NAME                STATUS
------------------------------ --------
NEW_PK_ID                      DISABLED
1
2
3
4
5
6
7
8
9

6、删除主键约束

• alter table 表名 drop constaint 主键名

SQL> alter table userinfo drop constraint new_pk_id;

表已更改。

SQL> select constraint_name,status from user_constraints where table_name='USERINFO';

未选定行
1
2
3
4
5
6
7

四、外键约束

1、自动删除主键约束

• alter table 表名 drop primary key;

SQL> desc userinfo_p
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(6)
 USERNAME                                           VARCHAR2(20)
 USERPWD                                            VARCHAR2(20)
 
SQL> alter table userinfo_p drop primary key;

表已更改。

SQL> desc userinfo_p
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(6)
 USERNAME                                           VARCHAR2(20)
 USERPWD                                            VARCHAR2(20)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2、在创建表时设置外键约束

• create table 表名1(列名 数据类型 references 表名2(列名),…)
  • 从表中外键字段的值必须来自主表中的相应字段的值，或者位null值
  • table1从表，在创建表时设置外键约束
  • table2主表，主从表中相应的字段必须时同一个数据类型

SQL> create table typeinfo (typeid varchar2(10) primary key, typename varchar2(20));

表已创建。

SQL> create table userinfo_f (id varchar2(10) primary key, username varchar2(20), typeid_new varchar2(10) references typeinfo(typeid));

表已创建。

SQL> insert into typeinfo values(1,1);

已创建 1 行。

SQL> insert into userinfo_f(id,typeid_new) values(1,2);
insert into userinfo_f(id,typeid_new) values(1,2)
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.SYS_C0011167) - 未找到父项关键字

SQL> insert into userinfo_f(id,typeid_new) values(1,1);

已创建 1 行。

SQL> insert into userinfo_f(id,typeid_new) values(2,null);

已创建 1 行。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

• create table 表名 (字段名1 数据类型1, …, constraint 外键约束名 foreing key(列名) references 表名(列名) [on delete cascade];

SQL> create table userinfo_f2 (id varchar2(10) primary key, username varchar2(20), typeid_new varchar2(10),constraint fk_typeid_new foreign key(typeid_new)references typeinfo(typeid));

表已创建。

SQL> create table userinfo_f3 (id varchar2(10) primary key, username varchar2(20), typeid_new varchar2(10),constraint fk_typeid_new1 foreign key(typeid_new)references typeinfo(typeid) on delete cascade);

表已创建。
1
2
3
4
5
6
7

3、在修改表时添加外键约束

• create table 表名 add constraint 外键约束名 foreign key(列名) references 表名(列名) [on delete cascade];

SQL> create table userinfo_f4 (id varchar2(10) primary key, username varchar2(20), typeid_new varchar2(10));

表已创建。

SQL> alter table userinfo_f4 add constraint fk_typeid_alter foreign key(typeid_new) references typeinfo(typeid);

表已更改。
1
2
3
4
5
6
7

4、禁用外键约束

• disable|enable constraint 外键约束名;

SQL> select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_F4';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C0011172                   P ENABLED
FK_TYPEID_ALTER                R ENABLED

SQL> alter table userinfo_f4 disable constraint FK_TYPEID_ALTER;

表已更改。

SQL> select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_F4';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C0011172                   P ENABLED
FK_TYPEID_ALTER                R DISABLED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5、删除外键约束

• drop constraint 外键约束名;

SQL> alter table userinfo_f4 drop constraint FK_TYPEID_ALTER;

表已更改。

SQL> select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_F4';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C0011172                   P ENABLED
1
2
3
4
5
6
7
8
9

五、唯一约束

1、简介

• 唯一约束和主键约束的区别：
  • 主键字段值必须是非空的
  • 唯一约束允许有一个空值
  • 主键在每张表中只能有一个
  • 唯一约束在每张表中可以有多个

2、在创建表时设置唯一约束

• create table 表名 (字段名 数据类型 unique,…);

SQL> create table userinfo_u (id varchar2(10) primary key, username varchar2(20) unique, userpwd varchar2(20));

表已创建。
1
2
3

• create table 表名 (字段名1 数据类型1,… constraint 唯一约束名 unique(字段名));

SQL> create table userinfo_u1 (id varchar2(10) primary key, username varchar2(20), constraint un_username unique(username));

表已创建。
1
2
3

3、在修改表时添加唯一约束

• create table 表名 add constraint 唯一约束名 unique(字段名);

SQL> create table userinfo_u2 (id varchar2(10) primary key, username varchar2(20));

表已创建。

SQL> alter table userinfo_u2 add constraint un_username_new unique(username);

表已更改。
1
2
3
4
5
6
7

4、禁用唯一约束

• alter table 表名 disable|enable constraint 唯一约束名

SQL> alter table userinfo_u2 disable constraint UN_USERNAME_NEW;

表已更改。

SQL> select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_U2';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C0011178                   P ENABLED
UN_USERNAME_NEW                U DISABLED
1
2
3
4
5
6
7
8
9
10

5、删除唯一约束

• alter table 表名 drop constraint 唯一约束名;

SQL> alter table userinfo_u2 drop constraint UN_USERNAME_NEW;

表已更改。
1
2
3
4

六、检查约束

1、简介

• 时表中的值更加有实际意义

2、在创建表时设置检查约束

• create table 表名 (字段名 数据类型 check(表达式), …);

SQL> create table userinfo_c (id varchar2(10) primary key, username varchar2(20), salary number(5,0) check(salary>0));

表已创建。

SQL> insert into userinfo_c values(1,'aaa',-50);
insert into userinfo_c values(1,'aaa',-50)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SYSTEM.SYS_C0011180)

SQL> insert into userinfo_c values(1,'aaa',50);

已创建 1 行。
1
2
3
4
5
6
7
8
9
10
11
12
13

• alter table 表名(字段名1 数据类型1, …, constraint 约束名 check(表达式));

SQL> create table userinfo_c1 (id varchar2(10) primary key, username varchar2(20), salary number(5,0), constraint ck_salary check(salary>0));

表已创建。
1
2
3

3、在修改表时添加检查约束

• alter table 表名 add constraint 约束名 check(表达式);

SQL> create table userinfo_3 (id varchar2(10) primary key, username varchar2(20), salary number(5,0));

表已创建。

SQL> alter table userinfo_3 add constraint ck_salary_new check(salary>0);

表已更改。
1
2
3
4
5
6
7

4、禁止检查约束

• disable|enable constraint 约束名

SQL> desc userinfo_3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(10)
 USERNAME                                           VARCHAR2(20)
 SALARY                                             NUMBER(5)

SQL> select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_3';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C0011184                   P ENABLED
CK_SALARY_NEW                  C ENABLED

SQL> alter table userinfo_3 disable constraint ck_salary_new;

表已更改。

SQL> select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_3';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C0011184                   P ENABLED
CK_SALARY_NEW                  C DISABLED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5、删除检查约束

• drop constraint 约束名

SQL> alter table userinfo_3 drop constraint ck_salary_new;

表已更改。

1
2
3
4