Deleting the master table withour deleting the child tables

Hi

i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table.

regards

vinod.t.v


Software Engineer
Ushustech
Technopark
Trivandrum
India
0
vinod
8/9/2007 10:19:07 AM
asp.net.faq 380 articles. 0 followers. Follow

9 Replies
764 Views

Similar Articles

[PageSpeed] 57

You need to temporarily disable constraints on the table.
 

Disable constraints: ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
Enable constraints: ALTER TABLE MyTable CHECK CONSTRAINT ALL
  

 


If this post was useful to you, please mark it as answer. Thank you!
0
johram
8/9/2007 11:08:44 AM

Thanks johram i can try this solution, this is usefull


Software Engineer
Ushustech
Technopark
Trivandrum
India
0
vinod
8/9/2007 11:19:18 AM

A few cautions.... 

Keep in mind that this suggests that you should revisit your database design. The purpose of foreign key constraints is to protect data integrity. Deleting just the parent records suggests that you really don't need the constraint, since there are apparently exceptions to the requirement. There are other ways to enforce the relationship that allows exceptions, which constraints don't.

When you re-enable the contraints, you're likely to get errors, since referential integrity has been broken.

If you need to change the parent, change it to the new parent, then delete the old parent.

Don 


Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
8/9/2007 3:32:21 PM

Dear Don thanks fo ryour suggetions , problem here is i have a database that contains tables with relations, but there is no on delete cascade option given, i need to delete the all table records curresponding to the master entry how i wil do it please help, its very difficutl to find the botton level child table and delte one by one, hope you understood my problem


Software Engineer
Ushustech
Technopark
Trivandrum
India
0
vinod
8/10/2007 4:38:24 AM

I understand your problem, but disabling constraints and deleting the master table content will leave you with a number of orphans in your child tables. So you still have the problem.

Either you start deleting from the "lowest" child table, or you design a cascading delete operation from the master table and down. That will help you in the future.

To help you with the nesting thing, I can give you an example. Let's say you want to delete entries with id 1000 and 1002 from the master table.

If you have nesting in one level, you do: 

DELETE FROM ChildTable1 WHERE MasterId IN (1000, 1002)
DELETE FROM MasterTable WHERE ID IN (1000, 1002)

If you have nesting in two levels, you start with the lowest level and then go upwards

DELETE FROM ChildTable2 WHERE ChildId1 IN (SELECT Id FROM ChildTable1 WHERE MasterId IN (1000, 1002))
DELETE FROM ChildTable1 WHERE MasterId IN (1000, 1002)
DELETE FROM MasterTable WHERE ID IN (1000, 1002)

Good luck!


If this post was useful to you, please mark it as answer. Thank you!
0
johram
8/10/2007 7:51:42 AM

Its agood suggestion realy very help full hope this can help me


Software Engineer
Ushustech
Technopark
Trivandrum
India
0
vinod
8/13/2007 8:40:57 AM

I'm glad that Johram gave you the solution, but I'm confused. In your original post you said that you didn't want to delete the child table records.

So now it is okay to delete the child records????

Don 


Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
8/14/2007 7:16:53 PM

Hi

databse contains tables that are related to one another

as shown below,

                                             ParentTable

       Child1 child2                                                         Master1 Master2 

i need to delete tha parent table entry and curresponding chil1 and child2 entries butthe parent table also have relation with master1 and master 2 so here id ont want to delete these master records thats my probelm, i said its  good suggestion thats all hope you are clear with my problem need help

 

 

 


Software Engineer
Ushustech
Technopark
Trivandrum
India
0
vinod
8/16/2007 4:29:59 AM

But won't your master records be invalid if you delete the parent record?

You need to somehow change parent record of the two master records, otherwise this operation will be impossible, with constraints maintained.
 


If this post was useful to you, please mark it as answer. Thank you!
0
johram
8/16/2007 10:14:16 AM
Reply:

Similar Artilces:

Deleting Child Table Rows and not from the master table using SqlDataAdapter.Update()
I have two tables tblPerson and tblPhone. tblPerson has: PersonID Name tblPhone has: PhoneID PersonID PhoneNumber I am using SqlDataAdapter.Update to delete the rows from the tables. I am also using Typed DataSets and the relation between the two tables depends upon the PersonID. When I delete from the tblPerson the corresponding record of that person is also deleted from the tblPhone which is good. When I try to delete from the tblPhone I dont want the Person record to be deleted and it throws exception:  Failed to enable constraints. One or more rows contain ...

Showing table columns from multiple tables in Formview, but insert/update/delete rows of Master table
I have a page on which I have GridView and FomView control. On selection of a row in GridView the row gets displayed in the Item template of the FormVIew. The values are the result of inner join from multiple tables i.e. table Stock,Product and Workshop.  Please let me know how I could insert/update/delete data for the base table i.e. Stock table, but be able to see the column values from the associated tables on the Itemtemplate,Edittemplate and Inserttemplate of the FormView. Thanks,Anita Hi:   Take a look at this tutorial. It's for DetailsView and same ...

Delete row and delete from table
Hi I need, after delete a grid row, execute a delete statmentProtected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand Dim advogado As StringIf (e.CommandName = "Apagar") Thenadvogado = RTrim(e.CommandArgument.ToString)Apaga_Processos(advogado)End Ifend subPrivate Sub Apaga_Processos(ByVal advogado As String)Dim conn As SqlConnectionDim comm As SqlCommandDim connectionString As String = ConfigurationManager.ConnectionStrings("mssql2.tuganet.com").ConnectionStringconn = New Sql...

Problem with Entity Model after deleteing table: cannot delete in Store, there can't add same table again
Moved by moderator XIII: This question's more related to the designer of Visual Studio 2008. Questions about Entity Framework itself should be posted into the Data Access forum:  Hi,  I am having a problem creating an Entity Data Model using Visual Studio 2008. To reproduce... Create a new data model. Added some tables by selecting Update Model from Database. Look in the Model Browser, tables are added under Entity Types. Delete one of the tables, the table is gone from Entity Types but is still in the Model Store under Tables/Views Now, when I select Update Mo...

how to delete from Table A based on Table B
lets say i have 100 employees on Table A and I have 10 employees on Table B. I want to delete all the employees in Table A that are in Table B. Delete From TableA Where EmpNum = (Select EmpNum From TableB)The above SQL wont work but i am looking for something similar. any ideas? You should use IN as your subquery is returning multiple records.Delete From TableA Where EmpNum IN (Select EmpNum From TableB)***********************Dinakar NethiLife is short. Enjoy it.***********************...

LINQ: How to delete automatically on delete from association table
Hi, I'll try to explain my problem with an example:  In DB(ms swl server 2005) I've 2 tables:A(ID, SomeAText)B(AID, SomeBText) In LINQ TO SQL (in mdbd file) I've association:A.ID --- Association ---> B.AIDIn Table B we can have many rows is same AID valueAlso, I've GridView which represents all rows from A table with LinqDataSource.Is there way how to set in LINQ that when delete a row from A table,AUTOMATICALLY all rows from B with AID(which points to ID of the deleting row from A) to bedeleted too ? Thanks in advance! asp.netWHAT ARE YOU CODING RIGHT NOW?G...

The same table (Table) cannot be the child table in two nested relations.
Hi, I"m getting this error when trying to load an XML document from a third party. I don't have the option of asking them to rename anything. Here's what i'm trying to accomplish. DataSet _ds = new DataSet(); _ds.ReadXml(URL, XmlReadMode.InferSchema); I tried InferSchema, IgnoreSchema and, of course, Auto. I got the same error w/ Infer as Auto (above). With Ignore, I got an empty DataSet. Please help!!! Thanks, kyle.kyleRochehttp://www.kyleroche.com Get that url, and dump it to the browser. It seems like it's not meant to be loaded into a dataset. Or it's...

How to insert into two tables at (Master table and Detail table)
I was hoping that the asp.net/learn/dataaccess would have had a tutorial for inserting into a MasterRecord and DetailRecord.  I have a page that I am working on that is a Reciepe. The Master Record has details such as customer, mix name, create date. The Detail Records has Ingredient Name, Ingredient Amount, etc. I have been following the DAL and BLL concept of the asp.net/learn/dataccess tutorials. Does anyone know of an example that I can follow of setting up two tables and inserting into two tables from the same Button_click event. Thanks, John Hi jfeeney, You just need to reme...

Cannot delete table
hi, i'm trying to delete a table (suspected to be corrupted or with errors) into my database using Sybase Central but then it just hung. while trying to view the corresponding columns of the said table, again my server just hung and remains to be idle. my .db is in Sybase ASE 11.9.2 running in Window NT 4.0 kindly help. thanks. ������������������������� Rodelio R. Ampon Management Advisory Service DEPT. OF HEALTH rra@doh.gov.ph ������������������������� ...

Delete from 2 tables in one delete statement in SQL 2005
Hi,i want to delete rows from two tables using one sql statement (inner join).One table is tblEmployee and other is tblUser and i want to delete all record in both of table whose UserID is EE00001 Create a foreign key on the database table for that field, and set the cascade option to delete. When you delete from the main table, the corresponding entry from the dependent table will also be deleted.Please Mark As Answer posts that helped you."If we learn from our mistakes, I should be brilliant by now."MCTS - .NET Framework 2.0: Web Applications I agree with the previous p...

Deleting a table
I 'm using ASA 7.03.2046 and I have dropped all publications and I went to drop a table but I keep getting a message stating that this table has publications. There are NO publications in this database. Is there something I'm missing here. Thanks, Kevin Please Ignore this posting , I deleted ALL publications for SQL Remote but there was a Mobilink template that had the table in it that I was trying to drop. "Kevin C. Dunn" <no_spam_kevin.dunn@spsinc.com> wrote in message news:GfAGjg5QCHA.245@forums.sybase.com... > I 'm using ASA 7.03.2046 and I...

table being deleted
I have a hell of a strange problem. This has happened twice at first thought I must have done something but now again happened so am thinking something more sinister.... I have a table called sales which has twice been totally deleted. Have checked the transaction log which previously I didn't have and it shows the delete happened during the last person being logged on and the whole table was deleted row by row. Therefore it is impossible for the user to have done it so must assume either the db is at fault or an outside hacker???? No other tables are affected. Any ideas???? Am...

Deletes are not being deleted.
Using ASA 7.03 I am having a problem in deleting field service orders that have been completed. Just getting started with sync so I am probably making a dumb mistake or just leaving out a step. The download_delete_cursor script is: Select ordernumber from ServiceOrderEntryMaster where Tech = ? and Order_Complete = 'Y' This based on the verbose reply from the MobiLink Synchronization server is picking the correct work order ( number 326 ) and showing a Delete Row line in the log. But the work order is not being deleted on the remote side. What could I be doing wron...

All tables where deleted
I have been setting up DNN portals on my companys server.  I had a company site and a local boy scouts site. I had worked on these site for the last week, did not do a backup yet.  I am running DNN 3.1.1.Saturday I added SimpleChat_V3 and Gallery modules.  The DNN Gallery beta did not install.  I add SimpleChat to my contacts page. Played with different page positions. This was going good then I got 'page cannot be displayed'.  Monday morning I am looking at my DotNetNuke database and I only see system tables. All dnn user tables have been dropped! I can se...

Web resources about - Deleting the master table withour deleting the child tables - asp.net.faq

Deleting Online Predators Act of 2006 - Wikipedia, the free encyclopedia
( DOPA ) is a bill ( H.R. 5319 ) brought before the United States House of Representatives on May 9, 2006 by Republican Pennsylvania Representative ...

Facebook Deleting Inflammatory Content In India
Facebook has been complying with a request by the government of India and removing “inflammatory and hateful content” that has caused tens of ...

Deleting your Facebook page is foolish
... brag, trailing closely behind that fantastically annoying “I don’t even have a TV , never mind watch the drivel!” Let’s lay it out: deleting ...

AdWords Campaign Experiments: Applying or deleting your experimental changes - YouTube
To improve your AdWords experience, we're testing a new tool that helps you optimize your account. AdWords Campaign Experiments, or ACE, does ...

Deleting abusive online content a 'near impossible' task
POSTING offensive, harassing, intimidating or defamatory content online takes mere seconds, but getting it removed can take what feels like a ...

Paleo advocate Pete Evans accused of deleting critical posts on Facebook
Is Paleo Pete Evans is open to dietary debate? Some say he's not.

Microsoft on the threshold of deleting 'appalling' Windows 8 software
MICROSOFT plans to cut its losses on Windows 8, the latest version of the software that runs the majority of the world's computers, jettisoning ...

How to get iOS 8 without deleting everything on your phone
If you've tried downloading iOS 8, or know anyone who has already done so, you're probably familiar with the biggest complaint: The download ...

Dalton McGuinty staffers broke law by deleting gas plant emails
Senior staff members in the offices of both the Ontario energy minister and former premier Dalton McGuinty intentionally deleted emails about ...

Jason Kenney under fire for praising ‘perfect, unaccented English’, deleting tweet
Canada's Minister for Multiculturalism, Jason Kenney, caused an upset Monday after praising an 11-year-old Iraqi refugee who "already speaks ...

Resources last updated: 12/13/2015 7:41:05 PM