Home » SQL & PL/SQL » SQL & PL/SQL » ora-01779 :cannot modify a column which maps to a non key- preserved table (oracle 10g)
ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429743] Thu, 05 November 2009 10:47 Go to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
iam getting that error if i have more than one table in a view

create table jai1
(a number,
b varchar2(10))

create table jai2
(c number,
d varchar2(10));

ALTER TABLE jai1 ADD
CONSTRAINT jai1_PK
PRIMARY KEY
(a));

ALTER TABLE jai2 ADD (
CONSTRAINT jai1_FK
FOREIGN KEY (c)
REFERENCES jai1 (a));


create view jai2_v -- created on both tables jai1 and jai2
as
select a,b,c,d from jai1 j1,jai2 j2
where j1.a=j2.c

insert into jai2_v(a,b) -- a b are jai1 columns.
values(3,'h') -- not


insert into jai2_v(c,d) -- c d are jai1 columns . this insert is accepted as 1,'a' value exist in the parent table Jai1
values(2,'b')

insert into jai2_v(a,b,c,d) -- a b are jai1 columns ora-01779 error
values(3,'c',1,'a')
Re: ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429749 is a reply to message #429743] Thu, 05 November 2009 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
About the 1638th time this question is posted.
Please search BEFORE posting, it is described in details on AskTom.

Regards
Michel

[Updated on: Thu, 05 November 2009 11:44]

Report message to a moderator

Re: ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429750 is a reply to message #429743] Thu, 05 November 2009 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01779: cannot modify a column which maps to a non key-preserved table
 *Cause: An attempt was made to insert or update columns of a join view which
         map to a non-key-preserved table.
 *Action: Modify the underlying base tables directly.

Regards
Michel
Re: ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429787 is a reply to message #429750] Thu, 05 November 2009 16:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I love this post for two reasons:

1) the only thing worse that posting no sample test case, is posting a test case that does not work. I appreciate that you posted the test case, but you should have actually tested the code you were pasting in. There are syntax error in it. I have corrected them here for you.

2) *** I really love this post because it shows once again that bad database design causes problems.

Your second table JAI2 has a mistake in it. Fix the mistake and the problem will go away.

Good luck, Kevin

SQL> create table jai1
  2  (a number,
  3  b varchar2(10))
  4  /

Table created.

SQL> create table jai2
  2  (c number,
  3  d varchar2(10))
  4  /

Table created.

SQL> ALTER TABLE jai1 ADD 
  2  CONSTRAINT jai1_PK
  3  PRIMARY KEY
  4  (a)
  5  /

Table altered.

SQL> ALTER TABLE jai2 ADD
  2  CONSTRAINT jai1_FK 
  3  FOREIGN KEY (c) 
  4  REFERENCES jai1 (a)
  5  /

Table altered.

SQL> create view jai2_v -- created on both tables jai1 and jai2
  2  as
  3  select a,b,c,d from jai1 j1,jai2 j2
  4  where j1.a=j2.c
  5  /

View created.

SQL> insert into jai2_v(a,b) -- a b are jai1 columns. 
  2  values(3,'h')
  3  /
insert into jai2_v(a,b) -- a b are jai1 columns.
                   *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Re: ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429983 is a reply to message #429787] Fri, 06 November 2009 12:23 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Thanks kevin for your appreciation.
i had this code in TOAD. so i just copied it here. i executed create,insert, updates individually. hence i didnot worry about whether iam missing something.

i have seen in ask tom. i didnot get clear idea hence i posted here. so that i get clear information here.

thanks
jillu
Re: ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429984 is a reply to message #429983] Fri, 06 November 2009 12:26 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
what is problem with my table jai2 ?? should it have a primary key? is it the problem or anything else?
Re: ora-01779 :cannot modify a column which maps to a non key- preserved table [message #429988 is a reply to message #429984] Fri, 06 November 2009 13:17 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You got it buddy. Right on the nose. Good call. The JAI2 table has no primary key. As you will see, this is not just theory, it is about information. Constraints are not about enforcing uniquess or cardinality rules, they are about providing information about how the data should behave. Once informationn is made available, uniqueness and cardinalities and referential integrity can be enforced.

Without the necessary information, Oracle could not un-ambiguously determine what row was being created when you did your insert. Once the necessary information is provided, it can.


The good knews is we don't have to follow a convoluted set of rules to get it right. Though we could go beyond the basics, we can also stick with just the basics. Always provide the following constraints for every table:

1) primary key
2) unique key(s)
3) foreign key(s)

and always make sure there is an index for each constraint. In particular make sure your foreign keys have a supporting index (there are locking issues otherwise).

As an aside, it is not necessary that the index which supports a constraint be identical to the constraint. This is because the constraint provides the information, the index provides the implementation. Consider this:

create index I1 (c,d) on jai2;

The above index is non-unique and contains two columns. But I can do this:

alter table jai2 add primary key (c);

and guess what... the constraint will enforce uniquess on the attribue C in JAI2 and it will use the already existing non-unique index I1 to do it rather than create a new index for implementing the unique rule. This means you can take advantage of any index to support any constraint as long as the leading columns in the index are the same columns in the constraint (order is not important). Older versions of Oracle were not this smart but currently supported versions are.

Kevin

SQL> create table jai1
  2  (a number,
  3  b varchar2(10))
  4  /

Table created.

SQL> 
SQL> create table jai2
  2  (c number,
  3  d varchar2(10))
  4  /

Table created.

SQL> 
SQL> ALTER TABLE jai1 ADD 
  2  CONSTRAINT jai1_PK
  3  PRIMARY KEY
  4  (a)
  5  /

Table altered.

SQL> 
SQL> 
SQL> ALTER TABLE jai2 ADD
  2  CONSTRAINT jai1_FK 
  3  FOREIGN KEY (c) 
  4  REFERENCES jai1 (a)
  5  /

Table altered.

SQL> 
SQL> create view jai2_v -- created on both tables jai1 and jai2
  2  as
  3  select a,b,c,d from jai1 j1,jai2 j2
  4  where j1.a=j2.c
  5  /

View created.

SQL> 
SQL> insert into jai2_v(a,b) -- a b are jai1 columns. 
  2  values(3,'h')
  3  /
insert into jai2_v(a,b) -- a b are jai1 columns.
                   *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> 
SQL> alter table jai2 add primary key (c)
  2  /

Table altered.

SQL> 
SQL> insert into jai2_v(a,b) -- a b are jai1 columns. 
  2  values(3,'h')
  3  /

1 row created.

SQL> 
SQL> drop view jai2_v
  2  /

View dropped.

SQL> 
SQL> drop table jai2
  2  /

Table dropped.

SQL> 
SQL> drop table jai1
  2  /

Table dropped.

SQL> 
Previous Topic: Date Format
Next Topic: Oracle Standard vs Oracle Enterprise User
Goto Forum:
  


Current Time: Sun Dec 04 12:40:14 CST 2016

Total time taken to generate the page: 0.09939 seconds