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

最新推荐文章于 2024-10-28 06:00:00 发布

原创最新推荐文章于 2024-10-28 06:00:00 发布 · 585 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #经验 #函数 #需求

sql 同时被 2 个专栏收录

7 篇文章

订阅专栏

Oracle

7 篇文章

订阅专栏

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

之前介绍过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