Oracle-约束的使用详解

原创 于 2024-01-17 14:59:59 发布 · 2.4k 阅读 · 34 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#oracle #数据库

文章目录

1. 约束分类

  • 主键约束（primary key）：主键列的值可唯一确定表中的某一行，主键列必须具有唯一性、且不能为空。主键约束相当于 unique（唯一性约束） + not null（非空约束）
  • 唯一性约束（unique）：可用于单列或多列，保证唯一性，可以为空
  • 非空约束（not null）：不允许为空
  • 外键约束（foreing key）：某张表的列的值存在于另一张表的列
  • 检查约束（check）:自定义的一些检查规则

2. 约束的添加方式

  • 创建表时添加
    • 行级约束（约束名称默认）：直接跟在对应的列后面，不定义约束名称，系统默认。
    • 行级约束（指定约束名称）：直接跟在对应的列后面，并且指定约束名称，方便后期查找和使用。
    • 表级约束：不直接跟在对应的列后面，而是在所有列定义完后最后单独再指定约束。
  • 使用 alter 语句添加

3. 主键约束-primary key

3.1 行级约束（约束名称默认）

--行级约束（约束名称默认）
create table 表名 (
    列1  数据类型  primary key,
    列2  数据类型,
    列3  数据类型,
    ……
)

举例如下，创建完成后可查看相关约束信息。

--创建表 test_tab1，id 列为主键
create table test_tab1 (
   id number not null primary key,
   name varchar2(20)
)

--插入数据,检测主键约束
insert into test_tab1 values (1,'tom');
insert into test_tab1 values (1,'bob');

在这里插入图片描述
插入数据后，提示报错，因为id值不能重复，所以刚才创建的主键约束已经生效。
在这里插入图片描述

3.2 行级约束（指定约束名称）

--创建表 test_tab1，id 列为主键
create table 表名 (
    列1  数据类型  constraint 约束名称 primary key,
    列2  数据类型,
    列3  数据类型,
    ……
)

举例如下，创建完成后，可查看约束名称。

create table test_tab1 (
   id number not null constraint test_tab1_id_pk primary key,
   name varchar2(20)  
)

在这里插入图片描述

3.3 表级约束

create table 表名 (
   列1  数据类型  not null,
   列2  数据类型,
   列3  数据类型,
   constraint 约束名 primary key(列名)    
)

举例

create table test_tab1 (
   id number not null,
   name varchar2(20),   
   constraint test_tab1_id_pk primary key(id)     
)

3.4 alter语句添加主键约束

这种方式一般是在表结构创建完之后需要新增约束或后期需要添加新的约束时，会用 alter 语句来添加。

alter table 表名 add constraint 约束名称 primary key(列名);

举例如下，创建之后的结果同样和上述的结果一样。

create table test_tab1 (
   id number not null,
   name varchar2(20)
)
;
--alter语句添加主键约束
alter table test_tab1 add constraint test_tab1_id_pk primary key(id);

4. 唯一约束-unique

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

  • 表可包含多个唯一约束，但每个表只允许一个主键。
  • 唯一约束列可包含NULL值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用。
  • 与主键不一样，唯一约束不能用来定义外键。

4.1 行级约束（约束名称默认）

create table 表名(
    列1 数据类型 ,
    列2 数据类型 not null unique,  --此列设置为 唯一约束
    列3 数据类型
)

举例，创建表 test_tab2，给字段 work_num（工号）添加唯一约束。并插入两行数据，且 work_num 列的值重复。结果可以看到违反唯一约束条件。

--创建表 test_tab2
create table test_tab2(
    id number not null primary key, --主键
    work_num  number not null unique, --唯一约束
    name varchar2(20) 
)

--插入数据，前两行数据的 work_num 列的值重复
insert into test_tab2 values (1,1001,'ada');
insert into test_tab2 values (2,1001,'bob');

在这里插入图片描述

4.2 行级约束（指定约束名称）

create table 表名(
    列1 数据类型 not null primary key,
    列2 数据类型 not null constraint 约束名称 unique,  --此列设置为 唯一约束
    列3 数据类型
)

举例
还是以上面例子举例，更换约束的创建方式，最后再插入数据进行验证，结果可以看到违反了唯一约束。

create table test_tab2(
    id number not null primary key,
    work_num  number not null constraint test_tab2_work_num_uq unique,
    name varchar2(20) 
)
--插入数据，前两行数据的 work_num 列的值重复
insert into test_tab2 values (1,1001,'ada');
insert into test_tab2 values (2,1001,'bob');

在这里插入图片描述

4.3 表级约束

create table 表名(
    列1 数据类型 not null primary key,
    列2 数据类型 not null , 
    列3 数据类型,
    constraint 约束名称 unique(列名)
)

举例

create table test_tab2(
    id number not null primary key,
    work_num  number not null,
    name varchar2(20) ,
    constraint test_tab2_work_num_uq unique(work_num)
)

4.4 alter 语句添加唯一约束

alter table 表名 add constraint 约束名 unique (字段1,字段2,……);

如果你需要根据特定的条件设置唯一约束，可以在约束中添加WHERE子句，指定条件。例如：

alter table 表名 add constraint 约束名 unique (字段1,字段2,……) where (条件)

举例
用alter语句添加唯一约束，效果和上面一样。

create table test_tab2(
    id number not null primary key,
    work_num  number not null,
    name varchar2(20) 
)
;
alter table test_tab2 add constraint test_tab2_work_num_uq unique(work_num);

在这里插入图片描述

5. 检查约束-check

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。
创建表时，通过数据类型，限制了列中可保存的数据的类型。检查约束在数据类型内又做了进一步的限制。

5.1 行级约束（约束名默认）

create table 表名(
    列1 数据类型 not null primary key,
    列2 数据类型 not null check(条件),
    列3 数据类型 
)

举例
创建表test_tab3，给字段gerder（性别）添加检查约束，规定此列的值只能是 man 或者 women。结果如下。

create table test_tab3(
    id number not null primary key,
    gender varchar2(5) not null check(gender in ('man','women')),
    name varchar2(20) 
)
;
insert into test_tab3 values (1,'man','ada');
insert into test_tab3 values (1,'02','ada');

在这里插入图片描述

5.2 行级约束（指定约束名称）

create table 表名(
    列1 数据类型 not null primary key,
    列2 数据类型 not null constraint 约束名称 check(条件),
    列3 数据类型 
)

举例
添加方式如下，其结果同上，这里就不做过多的描述了。

create table test_tab3(
    id number not null primary key,
    gender varchar2(5) not null constraint test_tab3_gender_ck check(gender in ('man','women')),
    name varchar2(20) 
)

5.3 表级约束

create table 表名(
    列1 数据类型 not null primary key,
    列2 数据类型 not null,
    列3 数据类型 ,
    constraints 约束名称 check(条件)
)

举例

create table test_tab3(
    id number not null primary key,
    gender varchar2(5) not null,
    name varchar2(20) ,
    constraint test_tab3_gender_ck check(gender in ('man','women'))
)

5.4 alter语句添加检查约束

alter table 表名 add constraint 约束名 check (约束条件)

举例

alter table test_tab3 add constraint test_tab3_gender_ck check(gender in ('man','women'));

6. 非空约束-not null

非空约束，即所在列不能为空。上述介绍的过程中其实已经描述过（not null）。
非空约束只有两种添加方式，创建表时添加的行级约束、alter语句添加约束。没有表级约束这种方式。

6.1 行级约束（约束名称默认）

--行级约束（约束名称默认）
CREATE TABLE 表名(
    列1  数据类型   NOT NULL,
    列2  数据类型,
    列3  数据类型 
);

6.2 行级约束（指定约束名称）

--行级约束（指定约束名称）
CREATE TABLE 表名(
    列1  数据类型 constraint 约束名称  NOT NULL,
    列2  数据类型,
    列3  数据类型 
);

6.3 alter语句添加非空约束

alter table 表名 modify (列名 constraint 约束名 not null);

举例
在这里插入图片描述

7. 外键约束-foreign key

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。

除帮助保证引用完整性外，外键还有另一个重要作用。在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。

由于需要一系列的删除，因而利用外键可以防止意外删除数据。有的DBMS支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从Customers表中删除某个顾客，则任何关联的订单行也会被自动删除。

7.1 行级约束（约束名称默认）

--在表1的列1上定义了一个外键，因此该列只能接受表2的主键值（列2）。即表1中的列1的任何值都必须是表2的列2中的值。
--不指定约束名称
CREATE TABLE 表1 (
    列1  数据类型  REFERENCES 表2(列2),
    列2  数据类型
);

举例
这里我们先创建两张表，用户表和订单表。订单表中的 user_id必须存在于用户表中的user_id，这两者是有关联性的。则在创建订单表时，需要对订单表的user_id添加外键约束，引用的是用户表中的user_id。

用户表订单表
user_id （用户id ）user_id （下单用户id ）
user_name（用户姓名）order_num（订单号）
order_date（订单日期）
--创建用户表
create table tab_user (
    user_id number primary key,
    user_name varchar2(20)
);

--创建订单表
create table tab_orders (
    user_id number references tab_user(user_id), --添加外键
    order_num number primary key,
    order_date varchar2(20) 
)

--用户表插入数据
insert into tab_user values(100,'ada');
insert into tab_user values(101,'bob');
insert into tab_user values(102,'cendy');

--订单表插入数据
insert into tab_orders values(101,20001,'19900101'); --可正常插入数据

此时查看两个表中的数据如下。
在这里插入图片描述
在这里插入图片描述

下面这条数据插入时，会报错，如下图所示。因为插入的 user_id中不存在于用户表中。

insert into tab_orders values(1003,20002,'19900101');

在这里插入图片描述

7.2 行级约束（指定约束名称）

--指定约束名称
CREATE TABLE 表1 (
    列1 数据类型 constraint 约束名称 REFERENCES 表2(列2),
    列2  数据类型
);

举例
同上面的例子一样，这里只描述订单表的具体内容，用户表的创建同上，插入数据后的结果也同上，这里就不再写一遍了。

--创建订单表
create table tab_orders (
    user_id number constraint tab_orders_userid_fr REFERENCES tab_user(user_id),
    order_num number primary key,
    order_date varchar2(20) 
);

7.3 表级约束

--指定约束名称
CREATE TABLE 表1 (
    列1 数据类型 ,
    列2  数据类型,
    constraint 约束名称 foreign key(列1) REFERENCES 表2(列2)
);

举例
同上面的例子一样，这里只描述订单表的具体内容，用户表的创建同上，插入数据后的结果也同上，这里就不再写一遍了。

--创建订单表
create table tab_orders (
    user_id number,
    order_num number primary key,
    order_date varchar2(20) ,
    constraint tab_orders_userid_fr foreign key(user_id) REFERENCES tab_user(user_id)
);

7.4 alter语句添加约束

alter table 表1 add constraint foreign key (字段1) references 表2 (字段2);

举例

alter table tab_orders add constraint tab_orders_userid_fr foreign key(user_id) REFERENCES tab_user(user_id);

7.5 外键删除分类

如上面的例子所述，用户表的user_id和订单表的user_id有关联关系。所以当删除用户表或删除用户表中的user_id时，订单表中的user_id该如何处理。主要有以下三种情况。

  • 强制不让删除：即删除用户表的user_id时，不让删除。
  • 自动设为null：即当删除了用户表的user_id时，订单表中的user_id值自动改为null。
  • 级联删除：即当删除了用户表的user_id时，订单表中的此用户的相关数据也同时删除。
 --强制不让删除
alter table 表1 add constraint 约束名 foreign key(列1) references 表2(列2);  
 --自动设为null
alter table 表1 add constraint 约束名 foreign key(列1) references 表2(列2) on delete set null;   
--级联删除
alter table 表1 add constraint 约束名 foreign key(列1) references 表2(列2) on delete cascade;

举例
现在两个表中的数据分别如下。
在这里插入图片描述
在这里插入图片描述
（1）现在给订单表添加外键约束，并设置为强制不让删除。
删除用户表中user_id=100的数据，结果如下。

alter table tab_orders add constraints tab_orders_user_id_fr foreign key(user_id) references tab_user(user_id);

--删除用户表中user_id=100的数据
delete from tab_user where user_id = 100;

在这里插入图片描述

（2）还是上面的例子，但当用户表中的user_id删除时，订单表中的user_id自动改为 null。

alter table tab_orders add constraints tab_orders_user_id_fr 
foreign key(user_id) references tab_user(user_id) on delete set null;

在这里插入图片描述
（3）还是上面的例子，但当用户表中的user_id删除时，订单表中的也同时删除此用户的相关信息。

alter table tab_orders add constraints tab_orders_user_id_fr 
foreign key(user_id) references tab_user(user_id) on delete cascade ;

delete from tab_user where user_id = 101;

在这里插入图片描述

8. 删除约束

alter table 表名 drop constraint 约束名;

举例

alter table tab_orders drop constraint tab_orders_user_id_fr;

以上就是oracle中关于约束的内容。

感谢大家阅读查看，如有补充和建议，欢迎留言~期待关注、点赞、收藏😘

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值