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
Post Reply
João
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Mon, 01 Aug 2005 7:55 am
Location: Lisboa

Hello everyone

Can anyone help me find a solution for the proper functioning of a query?

I tried to build a query, through the join of two tables, which resulted to visualize the data previously inserted, but it turns out that when I try to insert new records, and write in the fields TblreneMica.dimbordo and Tblceramica. DimBase The following error message appears: ORA-01779: Can not modify a column that corresponds to a non key-preserved table.

The tables and query are as follows:

Table 1

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

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

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
compliments to all
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Well, according to the error:

Select all

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 resolve this Oracle error are: 
   1. Try updating the tables directly.

Have you tried to do what the manual says? Insert directly to the table and not in the view?
João
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Mon, 01 Aug 2005 7:55 am
Location: Lisboa

Yes, if I directly insert the data into the view works, it turns out that this view is connected to a form built in Microsoft Access, and what is intended to enter the data through the same form.

Is there any way to do it, or can only you make updates and data inserts directly on the tables?

once again obliged for attention
greetings
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

It works in simple views, without many joins.
Usually, you can only enter information in the fields from the main table. (in the fields of Join, it does not work)
João
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Mon, 01 Aug 2005 7:55 am
Location: Lisboa

So does it mean that it is not possible to have a form for insertion of records based on more than a table?

How can I solve this problem? For if together the tables in one scrutinized the structure of the system.

Thanks
Greetings
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Hello John!!!

Sorry, I do not know all the details about the non key-preserved. But below, I leave a link that can help you: [url=http://download-west.oracle.com/docs/cd ... g03sch.htm]CLIQUE_AQUI
to have access, have to have an account in http://otn.oracle.com > is free.

I will also leave the HTML of this page here: [url=http://en.glufke.net/oracle/download/ad ... served.zip]Download_Zipado

Good Luck !!!
Send us what you discovered! :-O
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 3 guests