key_preserve_table.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 32 行

Key-Preserved Tables
The concept of a key-preserved table is fundamental to understanding
the restrictions on modifying join views. A table is key preserved 
if every key of the table can also be a key of the result of the join. 
So, a key-preserved table has its keys preserved through a join. 

多对一的多方为Key-Preserved Tables。

--没有PK
CREATE TABLE sm_saleorderlist(
    TransactionID NUMBER(10) NOT NULL UNIQUE,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    SaleTime DATE NOT NULL,
    );

DROP TABLE SM_EMP;
CREATE table sm_emp
(EmpID CHAR(10) ,
Name   VARCHAR2(10) ,
salary NUMBER(8,2),
TelNo  CHAR(8));

CREATE OR REPLACE VIEW sm_saleorderview AS 
SELECT sm_saleorderlist.TransactionID,sm_saleorderlist.TotalPrice ,sm_saleorderlist.EmployID ,
sm_emp.Name,sm_emp.TelNo,
sm_saleorderlist.SaleTime
FROM sm_saleorderlist,sm_emp
WHERE sm_saleorderlist.EmployID=sm_emp.empid;

SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME='SM_SALEORDERVIEW';