Re: Probably a simple INSERT operation question

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 3 Jan 2007 07:33:58 -0800
Message-ID: <1167838438.849107.46220@48g2000cwx.googlegroups.com>

Bruce C. Miller wrote:
> If I have a table that has no primary key (in my case, just two
> columns, one non-nullable), how do I perform an INSERT into it? If I
> just do a:
>
> INSERT INTO T1(V1, V2) VALUES('test', 'ghey');
>
> I get an error ORA-01779: cannot modify a column which maps to a
> non-key-preserved table. Running a search for this ORA error gives the
> solution "Try updating the tables directly." What kind of statement
> would I use for that?

It sounds like you are trying to update a complex view. See below for an example...

SQL> create table t0103a(c number, d number);

Table created.

SQL> create table t0103b(c number, d number);

Table created.

SQL> create view v0103 as select t0103a.c,t0103b.d from t0103a,t0103b;

View created.

SQL> insert into v0103 values(1,1);
insert into v0103 values(1,1)
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> select text from user_views where view_name = 'V0103';

TEXT

---

select t0103a.c,t0103b.d from t0103a,t0103b

SQL> Once you get the text for the view, you'll see what is being done "under the hood".

You could also look into an INSTEAD OF TRIGGER

HTH, Steve Received on Wed Jan 03 2007 - 09:33:58 CST