ORA-01779: non key-preserved

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 01 Aug 2005 8:38 am

Hi all does anyone can help me find a solution for the proper functioning of a query?

I tried to build a query using the join of two tables, which worked to show the data previously entered, but the thing is when I try to insert new records, and write in the fields tblceramica and tblceramica. dimbase dimbordo. pops up the following error message: ORA-01779: cannot modify a column that matches a non key-preserved table.

The tables and the query are as follows: table 1
Code: Select all
CREATE TABLE TBLARTEFACTO
(
  IDCODIGOARTEFACTO         NUMBER(11)          PRIMARY KEY,
  NUMEROINVENTARIONACIONAL  VARCHAR2(50 BYTE),
  NUMEROINVENTARIO          VARCHAR2(50 BYTE),
  IDCLASSE                  VARCHAR2(50 BYTE),
  IDCATEGORIA               VARCHAR2(50 BYTE),
  IDMATERIAL                VARCHAR2(50 BYTE),
  IDTIPOLOGIA               NUMBER(11),
  LOTE                      VARCHAR2(50 BYTE),
  NUMEROFRAGMENTOS          NUMBER(11)          DEFAULT 0,
  CRONOLOGIA                VARCHAR2(50 BYTE),
  COMPRIMENTO               FLOAT(22),
  ALTURA                    FLOAT(22),
  LARGURA                   FLOAT(22),
  ESPESSURA                 FLOAT(22),
  DIAMETRO                  FLOAT(22),
  PESO                      FLOAT(22),
  MARCAS                    VARCHAR2(50 BYTE),
  IDPROVENIENCIA            VARCHAR2(50 BYTE),
  IDDEPOSITO                VARCHAR2(50 BYTE),
  IDCOLOCACAO               VARCHAR2(50 BYTE),
  INCORPORACAO              VARCHAR2(50 BYTE),
  DATAINCORPORACAO          VARCHAR2(50 BYTE),
  DATASAIDA                 VARCHAR2(50 BYTE),
  IDESTATUTOPROPRIEDADE     VARCHAR2(50 BYTE),
  OBSERVACOES               CLOB,
  IDCODIGOCONTEXTO          VARCHAR2(50 BYTE),
  IDCODIGOACHADO            VARCHAR2(50 BYTE),
  IDCODIGODOACAO            VARCHAR2(50 BYTE)
)
table 2
Code: Select all
CREATE TABLE TBLCERAMICA
(
  IDCODIGOARTEFACTO  NUMBER(11),   PRIMARY KEY
  DIMBORDO           FLOAT(22),
  DIMBASE            FLOAT(22)
)
ALTER TABLE TBLCERAMICA ADD (
  CONSTRAINT TBLCERAMICATBLARTEFACTO FOREIGN KEY (IDCODIGOARTEFACTO)
    REFERENCES TBLARTEFACTO (IDCODIGOARTEFACTO));
View/Query
Code: Select all
CREATE OR REPLACE FORCE VIEW QRYCERAMICA
(IDCODIGOARTEFACTO, NUMEROINVENTARIONACIONAL, NUMEROINVENTARIO, IDCLASSE, IDCATEGORIA,
IDMATERIAL, IDTIPOLOGIA, LOTE, NUMEROFRAGMENTOS, CRONOLOGIA,
COMPRIMENTO, ALTURA, LARGURA, ESPESSURA, DIMBORDO,
DIAMETRO, DIMBASE, PESO, MARCAS, IDPROVENIENCIA,
IDDEPOSITO, IDCOLOCACAO, INCORPORACAO, DATAINCORPORACAO, DATASAIDA,
IDESTATUTOPROPRIEDADE, OBSERVACOES, IDCODIGOCONTEXTO, IDCODIGOACHADO, IDCODIGODOACAO)
AS
SELECT   tblartefacto.idcodigoartefacto,
         tblartefacto.numeroinventarionacional, tblartefacto.numeroinventario,
         tblartefacto.idclasse, tblartefacto.idcategoria,
         tblartefacto.idmaterial, tblartefacto.idtipologia, tblartefacto.lote,
         tblartefacto.numerofragmentos, tblartefacto.cronologia,
         tblartefacto.comprimento, tblartefacto.altura, tblartefacto.largura,
         tblartefacto.espessura, tblceramica.dimbordo, tblartefacto.diametro,
         tblceramica.dimbase, tblartefacto.peso, tblartefacto.marcas,
         tblartefacto.idproveniencia, tblartefacto.iddeposito,
         tblartefacto.idcolocacao, tblartefacto.incorporacao,
         tblartefacto.dataincorporacao, tblartefacto.datasaida,
         tblartefacto.idestatutopropriedade, tblartefacto.observacoes,
         tblartefacto.idcodigocontexto, tblartefacto.idcodigoachado,
         tblartefacto.idcodigodoacao
    FROM tblartefacto LEFT JOIN tblceramica
ON tblceramica.idcodigoartefacto = tblartefacto.idcodigoartefacto
   WHERE idcategoria = 'Cerâmica'
thanks for the attention greetings to all
João
Location: Lisboa

Poston Mon, 01 Aug 2005 11:34 am

Well, according to the error: Error: ORA-01779: cannot modify a column which maps to a non-key-preserved table : Cause You tried to INSERT or UPDATE columns in a join view which map to a non-key-preserved table.
Action: The options to solve this Oracle error are: 1. Try updating the tables directly.


I tried to do what the manual says? Insert directly in the table, and not on VIEW?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 01 Aug 2005 12:44 pm

Yes, if I insert the data directly in the view works, it turns out that this view is attached to a form built in microsoft access, and the intention is to enter the data via the same form.

Is there any way to do it, or it is only possible to do updates and inserts of data directly in tables?

Thanks again for your time kind regards
João
Location: Lisboa

Poston Mon, 01 Aug 2005 1:24 pm

Works on simple without too many views joins.
Normally, you can only enter information in the fields of the main table of the VIEW. (in the fields of join, does not work)
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 02 Aug 2005 5:29 am

So that means that it is not possible to have a form for entering records based on more than one table?

How can I solve this problem? As if along the tables in one desnormalizo the structure of the system.

Thanks Regards
João
Location: Lisboa

Poston Wed, 03 Aug 2005 8:41 am

Hi John!!!

I'm sorry, I don't know all the details about the non key-preserved. But below, I leave a link that may help you: [url = ://download-west http . . oracle com/docs/cd/B10501_01/./a96590/920 appdev adg03sch htm .] CLIQUE_AQUI [/url] To gain access, you must have an account on http://otn.oracle.com-> it's free.

I'll leave the HTML of this page here: [url = : http//www . glufke . net/oracle/download/zip . adg03sch_non_key_preserved] Download_Zipado [/url] good luck!!!
Send us what you found! :-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 5 guests