ROWNUM函数实现排名环比(一) 关于约束

本文详细介绍了Oracle数据库中外键约束的创建与管理方法,包括如何指定不同类型的删除规则,如普通删除、级联删除和置空删除等。此外,还介绍了如何通过USER_CONSTRAINTS视图查看约束状态。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

之前介绍过UNION ALL实现销售同环比的方法,今天介绍一种使用JOIN连接查询实现同环比的方式。同时,我们引入ROWNUM伪列,实现销售排名的显示。

在创建测试数据的时候,意外发现了一些关于约束的问题,所以,我准备在这篇文章中,先阐述一些关于Oracle约束,尤其是外键约束的事项。

需要直接看ROWNUM实现排名环比的同仁,可以直接移步《ROWNUM函数实现排名环比(二)》

再次声明,本篇文章是关于约束的介绍。

接下来,我们还原测试数据的创建过程。


一般情况下,软件都会有一张关于店仓档案的表,我们先创建该表

SQL> create table m_store(
     id number(4,0) primary key,name varchar2(10) not null);
--创建父表
SQL> select * from m_store;

           ID  NAME
  ----------  ----------
      1001  shop1
      1002  shop2
      1003  shop3
      1004  shop4
      1005  shop5


SQL> create table retail_s(
     id number(6,0) primary key
     ,billdate varchar2(6) unique
     ,store_id number(4,0) references M_STORE(ID)
     ,amount number(8,2));
--创建子表,子表中包含一个外键约束关联父表主键

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

CONSTRAINT_NAME     C STATUS
    ------------------------------ - --------
SYS_C0011478               P ENABLED
SYS_C0011479               U ENABLED
SYS_C0011480               R ENABLED

这样创建的约束没有名字,将来比较麻烦,
当我们想禁用\启用约束时,必须使用约束的名字,
所以应该在创建的时候取名字

SQL> create table retail_s(
     id number(6,0) constraint pk_id primary key
     ,billdate varchar2(6) constraint un_date unique
     ,store_id number(4,0) constraint fk_m_store_id references M_STORE(ID)
     ,amount number(8,2) constraint no_amount not null);

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

CONSTRAINT_NAME      C STATUS   DELETE_RU
------------------------------     - -------- ---------
NO_AMOUNT                   C ENABLED
PK_ID                                 P ENABLED
UN_DATE                          U ENABLED
FK_M_STORE_ID            R ENABLED  NO ACTION

创建成功
在看USER_CONSTRAINTS视图时,发现DELETE_RULE字段
该字段规定了外键约束的三种删除方式

SQL> INSERT INTO RETAIL_S VALUES(10001,201705,1001,50000);

1 row created.

先向子表中插入一条数据


SQL> DELETE M_STORE WHERE ID=1001;

ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_M_STORE_ID) violated - child record found
删除父表数据时出错,发现子表中的记录关联了要删除的数据,所以不允许

引申外键的DELETE_RULE机制
①普通删除机制(如果存在子表引用父表主键,则无法删除父表记录,默认建立该机制)
SQL> ALTER TABLE RETAIL_S ADD CONSTRAINT FK_M_STORE_ID FOREIGN KEY(STORE_ID) REFERENCES M_STORE(ID);

②级联删除机制(可删除存在引用的父表记录,而且同时把所有有引用的子表记录也删除)
SQL> ALTER TABLE RETAIL_S ADD CONSTRAINT FK_M_STORE_ID FOREIGN KEY(STORE_ID) REFERENCES M_STORE(ID) ON DELETE CASCADE;

③置空删除机制(可删除存在引用的父表记录,同时将子表中引用该父表主键的外键字段自动设为NULL,但该字段应允许空值)
SQL> ALTER TABLE RETAIL_S ADD CONSTRAINT FK_M_STORE_ID FOREIGN KEY(STORE_ID) REFERENCES M_STORE(ID) ON DELETE SET NULL;

测试置空删除机制

SQL> ALTER TABLE RETAIL_S DROP CONSTRAINT FK_M_STORE_ID;
不能直接修改,必须先删除,再重建

SQL> ALTER TABLE RETAIL_S ADD CONSTRAINT FK_M_STORE_ID FOREIGN KEY(STORE_ID) REFERENCES M_STORE(ID) ON DELETE SET NULL;

重新建立外键,并使用置空删除机制

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


CONSTRAINT_NAME                C STATUS   DELETE_RU
------------------------------ - -------- ---------
NO_AMOUNT                       C ENABLED
PK_ID                           P ENABLED
UN_DATE                        U ENABLED
FK_M_STORE_ID                   R ENABLED  SET NULL

SQL> DELETE M_STORE WHERE ID=1001;

1 row deleted.
删除成功

SQL> SELECT * FROM RETAIL_S;

        ID BILLDA   STORE_ID     AMOUNT
---------- ------ ---------- ----------
     10001 201705                  50000

原外键约束的数据被置空
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值