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

相关阅读:
react-starter脚手架搭建过程
 【DW组队学习—动手学数据分析】第二章：第一节数据清洗及特征处理-课程学习
 10.12 校招实习内推面经
 TMS WEB Core 2.0.2 Retail
事件循环
 基于视频技术与AI检测算法的体育场馆远程视频智能化监控方案
 java92-线程的创建方法一
 Typecho用宝塔面板建站(保姆级教程)
第2章 Hive安装
 校验 ChatGPT 4.0 真实性的三个经典问题：快速区分 GPT3.5 与 GPT4，并提供免费测试网站
原文地址：https://blog.csdn.net/s17856147699/article/details/126920143

Oracle的约束

文章目录

Oracle的约束

一、简介

二、非空约束

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

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

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

三、主键约束

1、简介

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

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

4、更改主键约束的名称

5、禁用主键约束

6、删除主键约束

四、外键约束

1、自动删除主键约束

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

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

4、禁用外键约束

5、删除外键约束

五、唯一约束

1、简介

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

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

4、禁用唯一约束

5、删除唯一约束

六、检查约束

1、简介

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

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

4、禁止检查约束

5、删除检查约束