Clearing a table which has more than 100 FK relationships

  • Hi All,

    I have one table which has relation to more than 100 tables which holds the company details. I've cleared all related table data and tried to clear this table. But it is not allowing me to delete/truncate or drop the table and shows the message

    " Msg 8621, Level 17, State 2, Line 1

    The query processor ran out of stack space during query optimization. Please simplify the query."

    Please help me...

  • Drop the FK's

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks for the quickie.. ๐Ÿ™‚

    But this is not possible..

  • if you really want to drop the table, you can script the drop by right click on the table in Management Studio (SSMS)......

    But it seems you have complicated things and written a delete statement which the SQL Server is having problem to resolve and execute.....can you give us the query you are trying to execute? We can try solving it....at least 'will have a better idea of the situation.....

    Chandrachurh Ghosh
    DBA โ€“ MS SQL Server
    Ericsson India Global Services Limited
    Quality is not an act, it is a habit.

  • Things are not complicated..

    Everything is in straight line only...

    I have a table Employees which has EmployeeID As Primary Key.

    This table has foreign key reference to more than 100 tables.

    When I am trying to clear the data in Employee Table with the query,

    'Delete From Employee' , it throws this error message and not allowing to execute. I already tried the delete option in management studio. But there also I am getting the same error... Hope the scenario is clear to you...

  • the problem is the logical design of the db - having 100 FK's is extremely suspect as far as good design goes !

    when the query processor compiles the

    DELETE FROM childtable

    it actually has to include all the code for triggers and to ensure DRI is not compromised

    - ie there would be no orphan child rows without parents

    if you use Query DisplayEstimatedExecutionPlan (ie Ctrl/L) in SSMS you will see the full extent of what it has to do

    there is a limit of about 250 tables in one query statement (before you get compile errors), so that is why it is failing for you (not just the humble DELETE itself!)

    some people have a propensity to over-index a table and to have unnecessary FK's in a db

    - eg if T1 -> T2 -> T3 it is usually unneccesary to have T1 -> T3

    get the logical design correct first and then the physical design and implementation will all work properly.

    BTW because of all the domain checks as above a DELETE can be slow. e.g. if you have

    create table Customers

    (custid int identity(1,1)

    ,custname varchar(255) not null

    ,CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (custid)

    )

    CREATE TABLE Orders

    (OrderID int IDENTITY(1,1) NOT NULL

    ,custid int NULL

    ,CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)

    ,CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)

    REFERENCES Customers (custid)

    )

    GO

    then you should also have this (on child table) to help query optimiser (especially for DELETE)

    CREATE NONCLUSTERED INDEX CustomersOrders ON Orders (custid)

    GO

    HTH

    Dick

  • Hi ..

    Thanks for the reply...

    But the problem remains unsolved.. How can I remove the relationships if it is required there... ? Also is it possible to remove the relationship temporarily ?

  • my point is that it extremely UNLIKELY that all your FK's are REQUIRED

    it is possible to temporarily disable a FK (for example to stuff in 200K rows)

    - but when you re-enable it must check ALL rows for compliance

    ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers

    if you want help with analysing your 100 FK's suggest you post your DDL in this forum

    - or post URL of an HTTP/FTP site where you have put it so people could [choose to] view

    Dick

  • Remove.....delete.....re-create......simple....

    else, check your where clause for the delete statement.....

    and you can never truncate a table with imposed constraints on it....you have to have to remove the constraints for that.....

    Chandrachurh Ghosh
    DBA โ€“ MS SQL Server
    Ericsson India Global Services Limited
    Quality is not an act, it is a habit.

  • Dear Sanuj,

    Try This....

    First execute below listed 2 queries and store the results

    Query1

    SELECT 'ALTER TABLE [' + o2.NAME + '] NOCHECK CONSTRAINT ' + o1.NAME

    FROM sys.foreign_keys o1

    INNER JOIN sys.objects o2

    ON o1.parent_object_id = o2.object_id

    AND o1.TYPE = 'F'

    AND o1.is_disabled = 1

    UNION ALL

    SELECT 'ALTER TABLE [' + o2.NAME + '] NOCHECK CONSTRAINT ' + o1.NAME

    FROM sys.foreign_keys o1

    INNER JOIN sys.objects o2

    ON o1.parent_object_id = o2.object_id

    AND o1.TYPE = 'F'

    AND o1.is_disabled = 0

    Query2

    SELECT 'ALTER TABLE [' + o2.NAME + '] CHECK CONSTRAINT ' + o1.NAME

    FROM sys.foreign_keys o1

    INNER JOIN sys.objects o2

    ON o1.parent_object_id = o2.object_id

    AND o1.TYPE = 'F'

    AND o1.is_disabled = 0

    1. Execute first queryโ€™s result set to temporarily remove the checking with FK Constraint

    2. Now Execute the Delete Query

    3. Now execute the result set of the second query to add the constraint again

  • Thanks man...!!!!

    It is solved....

    Long live SSC Forum.....!!!

  • Thank you very much for the response.

    This was really helpful and it worked.!!

    I got a similar solution from another SSC member...

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply