Re: Delete child-parent records???????

[andrewst]

Originally posted by Divya

Hi Tony,
Thanks for your help....
>
For the ON DELETE CASCADE...Can you just show me a simple example to
explain this better?
>
>

OK:-

SQLcreate table parent( parent_id number primary key, parent_name
SQLvarchar2(10) );

Table created.

SQLcreate table child( child_id number primary key, child_name
SQLvarchar2(10) ,
2 parent_id number,
3 constraint child_parent_fk foreign key (parent_id)
references parent
4 on delete cascade
5* );

Table created.

SQLinsert into parent values (1,'Fred');

1 row created.

SQLinsert into parent values (2,'Barney');

1 row created.

SQLinsert into child values (11,'Pebbles',1 );

1 row created.

SQLinsert into child values (21,'Bambam', 2);

1 row created.

SQLselect * from parent;

PARENT_ID PARENT_NAM
---------- ----------
1 Fred
2 Barney

SQLselect * from child;

CHILD_ID CHILD_NAME PARENT_ID
---------- ---------- ----------
11 Pebbles 1
21 Bambam 2

SQLdelete parent where parent_id=2;

1 row deleted.

SQLselect * from child;

CHILD_ID CHILD_NAME PARENT_ID
---------- ---------- ----------
11 Pebbles 1

--
Posted via http://dbforums.com

[Divya]

Re: Delete child-parent records???????

Hi Andrew,

Thanks for your help....

But there is a little issue here....these tables that have been
created by someone else and I dont think Mr. X has used the words "on
cascade delete" in his query while creating the child table....So can
i still find a way to delete child-parent records?

Regards,
Divya



andrewst <member14183@db forums.comwrote in message news:<3036817.1 056447364@dbfor ums.com>...

Originally posted by Divya

Hi Tony,
Thanks for your help....

For the ON DELETE CASCADE...Can you just show me a simple example to
explain this better?

OK:-
>
SQLcreate table parent( parent_id number primary key, parent_name
SQLvarchar2(10) );
>
Table created.
>
SQLcreate table child( child_id number primary key, child_name
SQLvarchar2(10) ,
2 parent_id number,
3 constraint child_parent_fk foreign key (parent_id)
references parent
4 on delete cascade
5* );
>
Table created.
>
SQLinsert into parent values (1,'Fred');
>
1 row created.
>
SQLinsert into parent values (2,'Barney');
>
1 row created.
>
SQLinsert into child values (11,'Pebbles',1 );
>
1 row created.
>
SQLinsert into child values (21,'Bambam', 2);
>
1 row created.
>
SQLselect * from parent;
>
PARENT_ID PARENT_NAM
---------- ----------
1 Fred
2 Barney
>
SQLselect * from child;
>
CHILD_ID CHILD_NAME PARENT_ID
---------- ---------- ----------
11 Pebbles 1
21 Bambam 2
>
SQLdelete parent where parent_id=2;
>
1 row deleted.
>
SQLselect * from child;
>
CHILD_ID CHILD_NAME PARENT_ID
---------- ---------- ----------
11 Pebbles 1