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  |
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 #429787 is a reply to message #429750] |
Thu, 05 November 2009 16:04  |
 |
Kevin Meade
Messages: 2103
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 #429988 is a reply to message #429984] |
Fri, 06 November 2009 13:17 |
|
Kevin Meade
Messages: 2103
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> |
|
|
|
Goto Forum:
Current Time: Fri Apr 26 13:09:56 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
