sm_emp_fk_cascade_new.txt

orale培训教材包括了所有的sql说明和实例

--超市管理，商品销售表
--要求商品售出时间必须在6点--20点之间。
--要求商品销售总价必须>0
--要求必须注明经手人的雇员号。
--并输入数据测试

--drop table sm_saleorderlist;
--if sm_emp exists,
--DROP TABLE sm_emp;

--建立sm_emp表----------------------------------------------------
CREATE table sm_emp
(EmpID CHAR(10) PRIMARY KEY,
Name   VARCHAR2(10),
salary NUMBER(8,2),
TelNo  CHAR(8));

INSERT INTO sm_emp VALUES('0000000001','张飞',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000002','关羽',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000003','刘备',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000007','007',100000,'62634546');
COMMIT;

--data sm_emp
EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
000000001  张飞            10000 62613546
000000002  关羽              100 62613547
0000000003 刘备            10000 62613547
0000000007 007                   62634567



--建立sm_saleorderlist表----------------------------------------------------
--建立商品销售表:sm_saleorderlist
CREATE TABLE sm_saleorderlist(
    TransactionID NUMBER(10) ,
    TotalPrice NUMBER(7,2) NOT NULL check (TotalPrice>0),
    EmployID CHAR(10) NOT NULL ,
    SaleTime DATE ,
    PRIMARY KEY(TransactionID),
    constraint 
	work_time 
	check( to_char(SaleTime,'HH24') between '06'  and '22'),
   constraint fk_emp FOREIGN KEY (EmployID) references sm_emp(empid));

insert into sm_saleorderlist values(1,12.9,'1111111111',sysdate)


alter table sm_saleorderlist disable constraint work_time;
--data sm_saleorderlist

INSERT INTO sm_saleorderlist 
VALUES(1,100.10,'0000000001',	to_date('09_9月_02 8:30:20' ,'dd_mon_yy hh24:mi:ss'));



INSERT INTO sm_saleorderlist VALUES(2,222.20,'0000000001',to_date('09_9月_02 8:30:20' ,'dd_mon_yy hh24:mi:ss'));
INSERT INTO sm_saleorderlist VALUES(3,300.10,'0000000002',to_date('09_9月_02 8:30:20' ,'dd_mon_yy hh24:mi:ss'));
INSERT INTO sm_saleorderlist VALUES(4,100.10,'0000000003',to_date('09_9月_02 8:30:20' ,'dd_mon_yy hh24:mi:ss'));
COMMIT;

SQL> select * from sm_emp;

EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
0000000001 张飞           100000 62613546
0000000002 关羽           100000 62613546
0000000003 刘备           100000 62613546
0000000007 007            100000 62634546

SQL> select * from sm_saleorderlist;

TRANSACTIONID TOTALPRICE EMPLOYID   SALETIME
------------- ---------- ---------- ----------
            1      100.1 0000000001 09-9月 -02
            2      222.2 0000000001 09-9月 -02
            3      300.1 0000000002 09-9月 -02
            4      100.1 0000000003 09-9月 -02



---------------------------------------------------------------------
--缺省状态，删除父表记录对子表的影响
--删除父表时，出现这种错误！
SQL> delete from sm_emp where empid='0000000001';
delete from sm_emp where empid='0000000001'
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (SCOTT.FK_EMP) - 已找到子记录日志
--出现这种情况，如何解决？应该先删除子表的对应纪录（sm_saleorderlist）


SQL> delete from sm_saleorderlist where employid='0000000001';

已删除2行。

SQL> commit;

提交完成。

SQL> select * from sm_emp;

EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
0000000001 张飞           100000 62613546
0000000002 关羽           100000 62613546
0000000003 刘备           100000 62613546
0000000007 007            100000 62634546

SQL> delete from sm_emp where empid='0000000001';

已删除 1 行。

SQL> commit;

提交完成。


--删除已有fk
alter table sm_saleorderlist drop constraint fk_emp;


---------------------------------
--on delete cascade 状态，删除父表记录对子表的影响
--改变fk_emp 为on delete cascade
alter table sm_saleorderlist add constraint fk_emp foreign key(EmployID) references 
sm_emp(empid) on delete cascade ;


SQL> select * from sm_saleorderlist;

TRANSACTIONID TOTALPRICE EMPLOYID   SALETIME
------------- ---------- ---------- ----------
            3      300.1 0000000002 09-9月 -02
            4      100.1 0000000003 09-9月 -02

--删除父表纪录
SQL> delete  from sm_emp where empid='0000000002';

已删除 1 行。
--检查sm_saleorderlist对应纪录是否delete cascade .
SQL> select * from sm_saleorderlist;

TRANSACTIONID TOTALPRICE EMPLOYID   SALETIME
------------- ---------- ---------- ----------
            4      100.1 0000000003 09-9月 -02



--rollback;之后，结果如何?

---------------------------------
--on delete set null 状态，删除父表记录对子表的影响
--改变fk_emp 为on delete set null

alter table sm_saleorderlist add constraint fk_emp foreign key (EMPLOYID) 
references sm_emp (empid) on delete set null;


SQL> delete from sm_emp where empid='0000000003';
delete from sm_emp where empid='0000000003'
            
ERROR 位于第 1 行:
ORA-01407: 无法更新 ("SCOTT"."SM_SALEORDERLIST"."EMPLOYID") 为 NULL
--why?

alter table sm_saleorderlist modify EMPLOYID null;



SQL> alter table sm_saleorderlist modify EMPLOYID null;

表已更改。

SQL> delete from sm_emp where empid='0000000003';

已删除 1 行。

SQL> select * from sm_emp;

EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
0000000003 刘备           100000 62613546
0000000007 007            100000 62634546

SQL> select * from SM_SALEORDERLIST;

TRANSACTIONID TOTALPRICE EMPLOYID   SALETIME
------------- ---------- ---------- ----------
            3      300.1            09-9月 -02
            4      100.1 0000000003 09-9月 -02

-rollback;


SQL>  select * from SM_SALEORDERLIST;

TRANSACTIONID TOTALPRICE EMPLOYID   SALETIME
------------- ---------- ---------- ----------
            3      300.1 0000000002 09-9月 -02
            4      100.1 0000000003 09-9月 -02