How to delete a row from parent table that has relationship with the child tables?

$enthil · #1 (**permalink**) 08-14-2007, 01:31 AM

There are 3 tables..

1. Parent
2. Child1
3. Child2

The tables child1 and child2 have foreign keys linked to the parent table. Suppose if i want to delete a row from parent table but it has some other rows in child1 and child2 with foreign key relationship.

Can anyone help me how to delete the row from parent table which is having some relation with child1 and child2? and if i delete that row from parent table, the related rows in child1 and child2 will also be deleted..

Plz help me...

prasath · #2 (**permalink**) 08-14-2007, 02:46 AM

Hi,
First,delete record from child1,child2 then delete record from parent table.In this way we achieve that one..........

Thanks,
Prasath.K

smani · #3 (**permalink**) 08-14-2007, 03:09 AM

hi,
in oracle Database it is available:
while you are creating foreign key, v have one option to give 'onDelete Cascade'

if you set this option then it automatically delete the child also

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
);

refer this url:
Oracle/PLSQL: Foreign Keys with cascade delete

Regards
Manivannan.S

write2ashokkumar · #4 (**permalink**) 08-14-2007, 03:33 AM

hi...

In mysql also we can use the "ON DELETE CASCADE" option while creating the foreign key.

Ex:

CREATE TABLE table_name
(column1 datatype,
column2 datatype ,
. . . . . . . . . . . . . .

FOREIGN KEY (column2,.....column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
);

If we use this option, we can delete the parent table data without consider the child table.

Regards,
S.Ashokkumar.

$enthil · #5 (**permalink**) 08-14-2007, 03:43 AM

hi smani

I worked with ur query and it works fine.. Thanks for ur help..

I have one more doubt, am using SQL Server project in Visual studio 2005. If i deployed the project, an assembly is created and is stored in SQL Server DB. But i want to use an external assembly in my SQL Server project. Is there any way to add an external assembly in SQL Server Database?

Murali · #6 (**permalink**) 08-14-2007, 09:04 AM

Quote:

Originally Posted by write2ashokkumar

hi...

In mysql also we can use the "ON DELETE CASCADE" option while creating the foreign key.

Ex:

CREATE TABLE table_name
(column1 datatype,
column2 datatype ,
. . . . . . . . . . . . . .

FOREIGN KEY (column2,.....column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
);

If we use this option, we can delete the parent table data without consider the child table.

Regards,
S.Ashokkumar.

Hi Ashok,

Code:

CREATE TABLE parent_table
(
 TABLE_ID	    NUMBER PRIMARY KEY,
 NAMES			VARCHAR(100) 
);


CREATE TABLE child_table
(
 TABLE_ID	    NUMBER PRIMARY KEY,
 PARENT_ID		NUMBER,
 NAMES			VARCHAR(100),
 FOREIGN KEY(PARENT_ID)REFERENCES PARENT_TABLE(TABLE_ID) ON DELETE CASCADE 
);


INSERT INTO PARENT_TABLE VALUES (1,'TEST');
INSERT INTO CHILD_TABLE VALUES(1,1,'TEST');

SELECT * FROM CHILD_TABLE;
SELECT * FROM PARENT_TABLE;


DELETE FROM PARENT_TABLE WHERE TABLE_ID = 1;

SELECT * FROM CHILD_TABLE;
SELECT * FROM PARENT_TABLE;

try out that code and find that ON DELETE CASCADE option deletes the parent_table even the child record exists and also deletes including the child records.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
What is the difference between DELETE TABLE and TRUNCATE TABLE commands in SQL Server	oxygen	Database Support	6	11-23-2007 06:17 AM
Need to close parent window after closing child window.	S.Vinothkumar	ASP and ASP.NET Programming	6	11-05-2007 06:08 AM
Is it possible to close the parent window (main web browser window) from a child win	gp_logesh	HTML, CSS and Javascript Coding Techniques	1	10-29-2007 04:53 AM
How to pass the value from the child window to parent window in javascript?	kingmaker	HTML, CSS and Javascript Coding Techniques	1	09-04-2007 03:18 AM
How can communicate between child and parent windows in VC++ win32 application?	kingmaker	C and C++ Programming	1	07-30-2007 12:51 AM