locked
Does dropping a table automatically drop its contraints and indexes? RRS feed

  • Question

  • When I have a table that has foreign key constraints and/or index and generate a delete script with Management Studio, the script explicitly drops all constraints/indexes before dropping the table.  Is this necessary?  Or does dropping a table automatically "cascade delete" all that table's constraints, indexes, etc...?
    Thursday, October 7, 2010 3:43 PM

Answers

  • Hi,

    As per Books On Line

    DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

    When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

    When we say DROP TABLE automatically drop constraints/triggers associated with table at the backend they also run these commands only (you can run profiler and confirm that). In SSMS this shows in script.

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++++++++++++++++++++++++++++++++++ Please mark "Propose As Answer" if my answer helped +++++++++++++++++++
    • Proposed as answer by Tom Phillips Thursday, October 7, 2010 6:57 PM
    • Marked as answer by Alex Feng (SQL) Wednesday, October 20, 2010 11:42 AM
    Thursday, October 7, 2010 3:47 PM

All replies

  • Hi,

    As per Books On Line

    DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

    When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

    When we say DROP TABLE automatically drop constraints/triggers associated with table at the backend they also run these commands only (you can run profiler and confirm that). In SSMS this shows in script.

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++++++++++++++++++++++++++++++++++ Please mark "Propose As Answer" if my answer helped +++++++++++++++++++
    • Proposed as answer by Tom Phillips Thursday, October 7, 2010 6:57 PM
    • Marked as answer by Alex Feng (SQL) Wednesday, October 20, 2010 11:42 AM
    Thursday, October 7, 2010 3:47 PM
  • The SSMS script is smart enough to include all the needed objects (e.g. FKs) to be dropped BEFORE dropping the table.

    Trying to delete it manually may produce "Could not drop object 'MyTable' because it is referenced by a FOREIGN KEY constraint"


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, October 7, 2010 6:51 PM