error inserting in master-detail

[amortillaro]

Hi guys: another fresh day, and a question..

I have this two tables:
CREATE TABLE Att_Test (FLD1 NUMBER(7,0) NOT NULL, FLD2 VARCHAR2(10) NULL, FLD3 VARCHAR2(10) NULL, FLD4 VARCHAR2(10) NULL, CHECK (FLD1 IS NOT NULL), PRIMARY KEY (FLD1)) ;
CREATE TABLE Att_TestChild (FLD1 NUMBER(7,0) NOT NULL, FLD2 NUMBER(2,0) NOT NULL, FLD3 VARCHAR2(10) NULL, FLD4 VARCHAR2(10) NULL, CHECK (FLD1 IS NOT NULL), CHECK (FLD2 IS NOT NULL), PRIMARY KEY (FLD1, FLD2), FOREIGN KEY (FLD1) REFERENCES "ATT_TEST"(FLD1)) ;
Look at the primary keys in both tables.
Then I try to execute the following insert statement:

Insert Into (Select Att_Test.fld1 Fld1, Att_TestChild.fld2 FldCh2, Att_Test.fld2 Fld2, Att_TestChild.fld3 FldCh3 From Att_Test, Att_TestChild Where (Att_Test.fld1=Att_TestChild.fld1))(Fld1, FldCh2, Fld2, FldCh3) Values (1,1,'a','a.a')

and get the following error
ORA-01779: cannot modify a column which maps to a non key-preserved table


What should I do in order to be able to insert the row in both tables? What about the "non key-preserved table" condition?

Thanks in advance, Aishel

 

[amortillaro]

As far as I have found, Access can insert in a master-detail view, it seems that it manage the workaround when the record already exist in the master part.
Oracle does not take care of that, so if the relation is not one to one, an error is raised.

Does somebody have more ideas about this?

...Aishel

 

[sem]

You may build a view based on that query and handle inserts manually by creating instead of trigger. You're right, Oracle is not able to insert into multiple tables from single statement.

Regards, Dima

http://seminihin.narod.ru