Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: ORA-01752: cannot delete from view without exactly one key-preserved table

    Hello All!
    I want to create a PROCEDURE deleting some records of the different TABLES.

    PHP Code:
    CREATE OR REPLACE PROCEDURE PRC_DEL_ALL_DAEMON_DATA
    (
           
    pId VIEW_RESERVE_DET.DAEMON%TYPE
    )
    AS
           
    LIC       NUMBER;
    BEGIN      

            DELETE FROM VIEW_
    ..
            IF 
    SQL%ROWCOUNT 0 THEN ..
            ELSE
                     
    DELETE FROM VIEW_FEATURE WHERE DAEMON pId;
                IF 
    SQL%ROWCOUNT 0 THEN ...
                ELSE
                    
    DELETE FROM VIEW_FEATURE_BORROW_OPTIONS WHERE DAEMON pId;
    ...
                    
    DELETE FROM VIEW_FEATURE_AVAIL WHERE DAEMON pId;
    ...        
                    ...            
    END IF;
                
                
    DELETE FROM VIEW_FEATURE_BASE WHERE DAEMON pId;
    ..        
            
    END IF;
            
    END;

    This procedure works till VIEW_FEATURE and VIEW_FEATURE_AVAIL.
    At this both views i have the same problem:

    PHP Code:
    DELETE FROM VIEW_FEATURE WHERE DAEMON ''

    ORA-01752: cannot delete from view without exactly one key-preserved table


    or

    PHP Code:
    DELETE FROM VIEW_FEATURE_AVAIL WHERE DAEMON ''

    ORA-01752: cannot delete from view without exactly one key-preserved table



    I don't know what how can i do to solve this problem without making many changes on the Database-structure..


    It would be great if anyone could help me!

    Thanks in advance!



    Maybe the codes of the both Tables & views can help:

    VIEW_FEATURE:

    PHP Code:
    CREATE OR REPLACE FORCE VIEW VIEW_FEATURE
    (ID_FEATUREFEATURE_KEYID_FEATURE_BASEFEATURE_NAMEDAEMON
     
    VERSIONEXPIRE_DATENUMLICNUM_AVAILCREATED_ON
     
    CREATED_BYCHANGED_ONCHANGED_BYSIGNVENDOR_STRING
     
    HOSTIDHOST_BASEDISSUEDSUPERSEDE_ISSUEDISSUER
     
    SUPERSEDE_ISSUERDUPGROUPSUPERSEDE_DUP_GRSNCK
     
    START_DATEUSER_BASEDTS_OKBORROWFLOAT_OK
     
    SUITE_DUPGROUPLINGERNOTICEOVERDRAFTPLATFORMS)
    AS 
    SELECT 
           F
    .ID_FEATUREF.FEATURE_KEYFB.ID_FEATURE_BASE
           
    FB.FEATURE_NAME FB.DAEMON  
         
    F.VERSIONF.EXPIRE_DATEF.NUMLICFA.NUM_AVAIL
         
    F.CREATED_ONF.CREATED_BYF.CHANGED_ONF.CHANGED_BY
         
    F.SIGNF.VENDOR_STRINGF.HOSTIDF.HOST_BASED
         
    F.ISSUEDF.SUPERSEDE_ISSUEDF.ISSUERF.SUPERSEDE_ISSUERF.DUPGROUPF.SUPERSEDE_DUP_GR
         
    F.SNF.CKF.START_DATEF.USER_BASEDF.TS_OKF.BORROWF.FLOAT_OK
         
    F.SUITE_DUPGROUPF.LINGERF.NOTICEF.OVERDRAFTF.PLATFORMS
    FROM FEATURE F
    VIEW_FEATURE_BASE FBFEATURE_AVAILABLE FA
    WHERE F
    .ID_FEATURE_BASE FB.ID_FEATURE_BASE
       
    AND FA.ID_FEATURE F.ID_FEATURE 
      
    AND FA.ID_FEATURE_BASE FB.ID_FEATURE_BASE

    TABLE FEATURE:
    PHP Code:

    CREATE TABLE FEATURE
    (
      
    ID_FEATURE        INTEGER,
      
    FEATURE_KEY       VARCHAR2(25CONSTRAINT NN_FEATURE_FKEY NOT NULL,
      
    ID_FEATURE_BASE   INTEGER CONSTRAINT NN_FEATURE_IDFBASE NOT NULL,
      
    VERSION           VARCHAR2(10CONSTRAINT NN_FEATURE_VERSION NOT NULL,
      
    EXPIRE_DATE       DATE CONSTRAINT NN_FEATURE_EXPDATE NOT NULL,
      
    NUMLIC            INTEGER CONSTRAINT NN_FEATURE_NUMLIC NOT NULL,
      
    CREATED_ON        DATE,
      
    CREATED_BY        VARCHAR2(20),
      
    CHANGED_ON        DATE,
      
    CHANGED_BY        VARCHAR2(20),
      
    SIGN              VARCHAR2(200),
      
    VENDOR_STRING     VARCHAR2(200),
      
    HOSTID            VARCHAR2(200),
      
    HOST_BASED        INTEGER,
      
    ISSUED            DATE,
      
    SUPERSEDE_ISSUED  VARCHAR2(1)                 DEFAULT 'N',
      
    ISSUER            VARCHAR2(50),
      
    SUPERSEDE_ISSUER  VARCHAR2(1)                 DEFAULT 'N',
      
    DUPGROUP          VARCHAR2(4),
      
    SUPERSEDE_DUP_GR  VARCHAR2(1)                 DEFAULT 'N',
      
    SN                VARCHAR2(25),
      
    CK                INTEGER,
      
    START_DATE        DATE,
      
    USER_BASED        INTEGER,
      
    TS_OK             VARCHAR2(1),
      
    BORROW            INTEGER,
      
    FLOAT_OK          VARCHAR2(10),
      
    SUITE_DUPGROUP    VARCHAR2(4),
      
    LINGER            INTEGER,
      
    NOTICE            VARCHAR2(200),
      
    OVERDRAFT         INTEGER,
      
    PLATFORMS         VARCHAR2(200)
    );


    CREATE UNIQUE INDEX PK_FEATURE ON FEATURE (ID_FEATURE);


    CREATE UNIQUE INDEX UK_FEATURE_FKEY ON FEATURE (FEATURE_KEY);


    CREATE OR REPLACE TRIGGER TRG_FEATURE_AFT_INS
    AFTER INSERT ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
          INSERT INTO FEATURE_AVAILABLE 
    (ID_FEATUREFEATURE_KEYID_FEATURE_BASE
                             
    NUM_AVAILEXPIRE_DATE
                        
    CREATED_ONCREATED_BYCHANGED_ONCHANGED_BY
                     

                  
    VALUES (SEQ_FEATURE.CURRVAL, :NEW.FEATURE_KEY, :NEW.ID_FEATURE_BASE
                             
    , :NEW.NUMLIC, :NEW.EXPIRE_DATE
                        
    SYSDATEUSERSYSDATEUSER
                     
    );

    END;
    /

    CREATE OR REPLACE TRIGGER TRG_FEATURE_AFT_UPD 
    AFTER UPDATE ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
         UPDATE FEATURE_AVAILABLE 
                                  SET FEATURE_KEY 
    = :NEW.FEATURE_KEY
                                   
    ID_FEATURE_BASE =:NEW.ID_FEATURE_BASE
                                        
    NUM_AVAIL =:NEW.NUMLIC
                                   
    EXPIRE_DATE =:NEW.EXPIRE_DATE
                                   
    CREATED_ON = :NEW.CREATED_ON
                                   
    CREATED_BY = :NEW.CHANGED_BY 
                                   
    CHANGED_ON SYSDATE
                                   
    CHANGED_BY USER
                               WHERE ID_FEATURE 
    = :NEW.ID_FEATURE;
    END;
    /




    CREATE OR REPLACE TRIGGER TRG_FEATURE_BEF_INS 
    BEFORE INSERT ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    tmpVar1 NUMBER;

    BEGIN
         tmpVar1 
    := 0;
         
      
    SELECT SEQ_FEATURE.NEXTVAL INTO tmpVar1 FROM DUAL;
        :NEW.
    ID_FEATURE := tmpVar1
        :NEW.
    CREATED_ON := SYSDATE;
        :NEW.
    CREATED_BY := USER;
        :NEW.
    CHANGED_ON := SYSDATE;
        :NEW.
    CHANGED_BY := USER;

    END;
    /



    CREATE OR REPLACE TRIGGER TRG_FEATURE_BEF_UPD 
    BEFORE UPDATE ON FEATURE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    tmpVar1 NUMBER;

    BEGIN
         tmpVar1 
    := 0;
         
      
    SELECT SEQ_FEATURE.NEXTVAL INTO tmpVar1 FROM DUAL;
        :NEW.
    CHANGED_ON := SYSDATE;
        :NEW.
    CHANGED_BY := USER;
    END;
    /

    ALTER TABLE FEATURE ADD (
      
    CONSTRAINT PK_FEATURE PRIMARY KEY (ID_FEATURE));


    ALTER TABLE FEATURE ADD (
      
    CONSTRAINT FK_FEATURE_FBASE FOREIGN KEY (ID_FEATURE_BASE
        
    REFERENCES FEATURE_BASE (ID_FEATURE_BASE)); 


    VIEW_FEATURE_AVAIL:

    PHP Code:
    CREATE OR REPLACE FORCE VIEW VIEW_FEATURE_AVAIL
    (DAEMONID_FEATUREFEATURE_KEYFEATURE_NAMEID_FEATURE_BASE
     
    VERSIONEXPIRE_DATENUMLICNUM_AVAILCREATED_ON
     
    CREATED_BYCHANGED_ONCHANGED_BYSIGNVENDOR_STRING
     
    HOSTIDHOST_BASEDISSUEDSUPERSEDE_ISSUEDISSUER
     
    SUPERSEDE_ISSUERDUPGROUPSUPERSEDE_DUP_GRSNCK
     
    START_DATEUSER_BASEDTS_OKBORROWFLOAT_OK
     
    SUITE_DUPGROUPLINGERNOTICEOVERDRAFTPLATFORMS)
    AS 
    SELECT 
           FB
    .ID_DAEMONF.ID_FEATUREF.FEATURE_KEY
           
    FB.FEATURE_NAME FB.ID_FEATURE_BASE  
         
    F.VERSIONF.EXPIRE_DATEF.NUMLICFA.NUM_AVAIL
         
    F.CREATED_ONF.CREATED_BYF.CHANGED_ONF.CHANGED_BY
         
    F.SIGNF.VENDOR_STRINGF.HOSTIDF.HOST_BASED
         
    F.ISSUEDF.SUPERSEDE_ISSUEDF.ISSUERF.SUPERSEDE_ISSUERF.DUPGROUPF.SUPERSEDE_DUP_GR
         
    F.SNF.CKF.START_DATEF.USER_BASEDF.TS_OKF.BORROWF.FLOAT_OK
         
    F.SUITE_DUPGROUPF.LINGERF.NOTICEF.OVERDRAFTF.PLATFORMS
    FROM FEATURE F
    FEATURE_BASE FBFEATURE_AVAILABLE FA
    WHERE F
    .ID_FEATURE_BASE FB.ID_FEATURE_BASE
      
    AND FA.ID_FEATURE F.ID_FEATURE 
      
    AND FA.ID_FEATURE F.ID_FEATURE

    Table FEATURE_AVAILBLE:

    PHP Code:

    CREATE TABLE FEATURE_AVAILABLE
    (
      
    ID_FEATURE       INTEGER,
      
    FEATURE_KEY      VARCHAR2(25),
      
    ID_FEATURE_BASE  INTEGER,
      
    NUM_AVAIL        INTEGER,
      
    EXPIRE_DATE      DATE,
      
    CREATED_ON       DATE,
      
    CREATED_BY       VARCHAR2(20),
      
    CHANGED_ON       DATE,
      
    CHANGED_BY       VARCHAR2(20),
      
    ID_ACT           INTEGER
    );

    CREATE UNIQUE INDEX PK_FEATURE_AVAILABLE ON FEATURE_AVAILABLE (ID_FEATURE);


    CREATE OR REPLACE TRIGGER TRG_FEATURE_AVAILABLE_BEF_UPD 
    BEFORE UPDATE ON FEATURE_AVAILABLE REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
         
    tmpVar1 NUMBER;

    BEGIN
         tmpVar1 
    := 0;
         
      
    SELECT SEQ_FEATURE_AVAILABLE.NEXTVAL INTO tmpVar1 FROM DUAL;
        :NEW.
    CHANGED_ON := SYSDATE;
        :NEW.
    CHANGED_BY := USER;
    END;
    /




    .....
    ALTER TABLE FEATURE_AVAILABLE ADD (
      
    CONSTRAINT PK_FEATURE_AVAILABLE PRIMARY KEY (ID_FEATURE)   );


    ALTER TABLE FEATURE_AVAILABLE ADD (
      
    CONSTRAINT FK_FAVAIL_FEATURE FOREIGN KEY (ID_FEATURE
        
    REFERENCES FEATURE (ID_FEATURE)
        
    ON DELETE CASCADE); 
    Last edited by julla27; 09-10-04 at 05:39.
    Regards,
    Julia

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to fully understand the concept of Key Preserved tables. Essentially this is a table whose rows map 1:1 with rows in the view. Take this very simple view:
    Code:
    create view v as
    select emp.ename, dept.dname
    from emp, dept
    where emp.deptno = dept.deptno;
    
    View created.
    
    SQL> select * from v;
    
    ENAME      DNAME
    ---------- --------------
    SMITH      RESEARCH
    ALLEN      SALES
    WARD       SALES
    JONES      RESEARCH
    MARTIN     SALES
    BLAKE      SALES
    CLARK      ACCOUNTING
    SCOTT      RESEARCH
    TURNER     SALES
    ADAMS      RESEARCH
    JAMES      SALES
    FORD       RESEARCH
    MILLER     ACCOUNTING
    
    13 rows selected.
    In this case, tabel EMP is key-preserved but table DEPT is not - because any EMP row appears just once (or not at all) in the view, but each DEPT row appears many times. So in this case "DELETE FROM v WHERE ename = 'JAMES'" would be transalated to "DELETE FROM emp WHERE ename = 'JAMES'".

    Oracle only allows one key-preserved table in an updatable view. If the view contains 2 or more key-preserved tables, then it is not updatable.

    For Oracle to determine whether the veiw is key-preserved, the tables must have primary and foreign keys defined - as yours do.

    Having said all that, could you not get around this problem by simply deleting from the appropriate base table in your procedure, instead of deleting from the view?

    If not, you can always resort to putting INSTEAD OF triggers on the views.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •