forum.venkateswarlu.net
      Register      Login
Delete records in Parent, Child table

3 replies to this topic

Gaurav Dutt #1
Member
62 Points
 Posted on 21 Mar 2012 12:53 PM IST Hi,
I want to delete records from my parent table(primary key) and child table(foreign key) at a time.

Please suggest me or me a query!

Thanks,
Gaurav 
SQL Server     928 views     Reply to this topic
Harikrishna Sikhakolli #2
Member
108 Points
 Replied on 22 Mar 2012 07:34 AM IST --Here dept is the parent table
--table name:Dept
id dname loc
10 Accounts hyd
20 S/w che
30 H/w ban
Here declare id column as primary key column.
Here primary key is on id column
--child table
--table name is Emp
empid name deptno
1 Hari 10
2 krishna 20
3 babu 10
Here deptno is the foreignkey
My aim is when we delete dept 10 in dept table the dependent records in child table deleted
--solution
Alter table Emp
ADD CONSTRAINT fk FOREIGN KEY (deptno) REFERENCES Dept(id) ON DELETE CASCADE  
Reply to this topic
Gaurav Dutt #3
Member
62 Points
 Replied on 26 Mar 2012 06:58 AM IST Thanks! 
Reply to this topic
Gaurav Dutt #4
Member
62 Points
 Replied on 05 Apr 2012 11:54 AM IST I create two table for this tab_purchase as parent and tab_inventory as child. And I provide FOREIGN KEY (product_id)
REFERENCES tab_purchase(id) on delete cascade in child table. By which i can delete records using single delete query in parent and child table.
create table tab_purchase
(
id int identity(1,1) not null,
name varchar(200),
qty varchar(50),
status varchar (100),
entry_date datetime
PRIMARY KEY (id)
)
Go
create table tab_inventory
(
id int identity(1,1) not null,
product_id int,
qty varchar(50),
price varchar(100),
status varchar(100),
entry_date datetime
FOREIGN KEY (product_id)
REFERENCES tab_purchase(id) on delete cascade
)
Go

================
Query:
1) delete from tab_purchase where id=2
2) delete t1 from tab_purchase t1 inner join tab_inventory t2
on t1.id=t2.product_id
and t1.id=3

3) delete t1 from tab_purchase t1 , tab_inventory t2
where t1.id=t2.product_id
and t1.name='Pen'

 
Reply to this topic