之前介绍过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
原外键约束的数据被置空