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]

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 <me*********@dbforums.comwrote in message news:<30****************@dbforums.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