Home » Developer & Programmer » Forms » deleting records (developer 6i second release)
deleting records [message #293870] Tue, 15 January 2008 06:43 Go to next message
ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq
Member
Dear all

I have a form that based on a relation one to many "cascade"
so how can I delete a parent record and his children
by a command button?

best regards
Re: deleting records [message #293875 is a reply to message #293870] Tue, 15 January 2008 07:08 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If foreign key constraint is created with the ON DELETE CASCADE option, just DELETE_RECORD; database will take care of the rest.
Re: deleting records [message #293880 is a reply to message #293870] Tue, 15 January 2008 07:37 Go to previous messageGo to next message
ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq
Member
Dear
what do u mean by foreign key constraint is created with the ON DELETE CASCADE option
but this command has deleted only the children records and not the parent
pls inform me
best regards
Re: deleting records [message #293883 is a reply to message #293880] Tue, 15 January 2008 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example: two tables, with a referential integrity constraint:
SQL> create table master
  2  (id number primary key,
  3   name varchar2(20));

Table created.

SQL> create table detail
  2  (id_fk number constraint fk_dm references master (id),
  3   value number);

Table created.

SQL> insert all
  2    into master (id, name) values (1, 'Little')
  3    into master (id, name) values (2, 'Foot')
  4    into detail (id_fk, value) values (1, 10)
  5    into detail (id_fk, value) values (1, 20)
  6    into detail (id_fk, value) values (2, 30)
  7  select * From dual;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from master;

        ID NAME
---------- --------------------
         1 Little
         2 Foot

SQL> select * from detail;

     ID_FK      VALUE
---------- ----------
         1         10
         1         20
         2         30

SQL>

Now let's try to delete one of the 'master' records: it will fail as child records still exist:
SQL> delete from master where id = 1;
delete from master where id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DM) violated - child record found

Our next step is to recreate the foreign key constraint and include the ON DELETE CASCADE option:
SQL> alter table detail drop constraint fk_dm;

Table altered.

SQL> alter table detail add constraint fk_dm foreign key (id_fk)
  2  references master (id) on delete cascade;

Table altered.

Deleting a master will now succeed:
SQL> delete from master where id = 1;

1 row deleted.

SQL> select * from master;

        ID NAME
---------- --------------------
         2 Foot

SQL> select * From detail;

     ID_FK      VALUE
---------- ----------
         2         30

SQL>
Re: deleting records [message #294005 is a reply to message #293870] Wed, 16 January 2008 00:18 Go to previous messageGo to next message
ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq
Member
Thanks you are great brother

pls tell me if I wanted to delete a record from the many table in a form how can I do that pls tell me and waiting for your advice

best regards;
Re: deleting records [message #294009 is a reply to message #294005] Wed, 16 January 2008 00:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

did you try ? if other tables also refer the master table then yes those records will also be deleted .continuation from @LITTLEFOOT's example :
added another table
create table detail2
   (id_fk number ,
     value number,
constraint fk_dm2 foreign key(id) references master(id) on delete cascade);


insert all
into detail2 values(1,100)
into detail2 values(2,50)


delete from master where id=1

select * from master
/
select * from detail
/
select * from detail2


regards,





Re: deleting records [message #294060 is a reply to message #293870] Wed, 16 January 2008 05:09 Go to previous messageGo to next message
ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq
Member
Dear

yes it will be deleted and thats is ok
but please tell me how to make it possible in the form ?

best regards
Re: deleting records [message #294076 is a reply to message #293875] Wed, 16 January 2008 06:19 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot wrote on Tue, 15 January 2008 14:08
If foreign key constraint is created with the ON DELETE CASCADE option, just DELETE_RECORD; database will take care of the rest.

Previous Topic: Source of Developer 6i
Next Topic: automating forms compilation
Goto Forum:
  


Current Time: Wed Dec 07 06:47:51 CST 2016

Total time taken to generate the page: 0.13215 seconds