Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: Need to delete a table which has a reference to another table

    Hi Friends...

    In general we cannot delete a table if it refers another table. But in the process of backup and restore, we need to delete all the tables, so we have return an asp.net function...

    Dim Datatable As String
    Dim ds As New DataSet
    ds = gDatabase.ExecuteQuery(" select Table_Name from Information_schema.Tables where Table_type='TABLE'")
    If ds.Tables.Count > 0 Then
    If ds.Tables(0).Rows.Count > 0 Then
    Dim dr As DataRow
    Dim da As SqlCeDataAdapter
    Dim Cmd As String
    For Each dr In ds.Tables(0).Rows
    Datatable = dr.Item("Table_name")
    Cmd = "Delete from [" & Datatable & "]"
    gDatabase.ExecuteNonQuery(Cmd)
    Next
    End If
    End If

    we got an error like

    "Msg 4712, Level 16, State 1, Line 1
    Cannot truncate table 'Electricalworks' because it is being referenced by a FOREIGN KEY constraint.
    "

    how to override these errors, if we have to delete all the tables. We have about 200 tables.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can disable the constraints.

    ALTER TABLE mytable NOCHECK CONSTRAINT ALL

    to re-enable them:

    ALTER TABLE mytable CHECK CONSTRAINT ALL

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I hope you don't have any tables named like this:

    sysobjects] where 1=0; drop database goo --


  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by maheswari19
    But in the process of backup and restore, we need to delete all the tables
    Hi

    Jesse is spot on however... are you rolling your own backup and restore? Can't you just use the in built backup and restore options and not worry about constraints?

  5. #5
    Join Date
    Dec 2007
    Posts
    3
    Hi jezemine....thanks for ur reply....here we are using mobile database, where we cannot disable constraints....tell me some other suggestions

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how about deleting all of your foriegn key tables first and then your primary key tables they reference. if you are deleting everything i have no idea why you could not disable and reenable constraints as jez mentioned.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm extremely suspicious that I'm missing something, but the solution isn't that hard from a set based perspective.

    Just delete from the tables in order of dependance... Take tables with no dependants in the first round, then tables that have only dependancies on the empty tables, etc.

    -PatP

  8. #8
    Join Date
    Dec 2007
    Posts
    3
    HI all

    I have concluded the scenario with "Thrasymachus" suggession...

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •