How come the message "Table is marked for deletion" keeps appearing?

Question

Using SQL Server 2008 and I'm doing some schema construction in the Management Studio designer.

I created a table early in the process called "Animal", but then decided to delete shortly after. I deleted it via the tree view in Management Studio (right-click delete).

Now I'm late in the process, I would like to recreate the table "Animal" but with different fields.

The second time round with Animal gets created fine, but when I try to add the table to my diagram I get the message "Table 'Animal' is marked for deletion, can't be added to the diagram or opened in the table designer".

What's the deal? How can I use this table normally again and not suffer from older table naming conflicts?

Alternatively, if the table is marked for deletion, can I actually invoke deletion somehow? Then recreate another table with the same name.

This is a little annoying. Would appreciate any help.

Answer 1

If you're using the schema tool, it may be simpler to recreate the schema. In my experience I've found that tool to have some odd quirks that have been fixed by simply recreating the schema ( the graphical view of it, not the whole db :) )

Answer 2

From MSDN, to delete a table from the Visual Database Designer:

1. In your database diagram, select the table you want to remove.
2. Right-click the table and choose Remove Table from Diagram from the shortcut menu.

-or-

Press the ESC key.

If the table has unsaved changes as a result of edits you made in the database diagram, a message prompts you to save the table before removing it.

The table is removed from your diagram but it continues to exist in the database.

So, to delete it, you'll have to do that from script or object explorer (MSDN):

To delete a table from the database

1. In Object Explorer, select the table you want to delete.
2. Right-click the table and choose Delete from the shortcut menu.
3. A message box prompts you to confirm the deletion. Click Yes.

Answer 3

What worked: rebooting.

Restarting the entire computer worked.

Simply restarting the db server didn't work, but rebooting the entire machine did.

I have no idea why!

Answer 4

I was able to fix this problem by closing SQL Management Studio and re-opening it. I bet simply severing the connection to the server and reinstating it would work.

Answer 5

Just Severing the connection from SQL Management Studio did not work. But restarting the SQL Management Studio worked. Still better than restarting the system.

Answer 6

I know this is old, but I came across this same problem. I found the solution here:

http://weblogs.asp.net/atlaszhu/archive/2010/04/04/sql-server-2008-database-diagram-quot-saving-changes-is-not-permitted-quot.aspx

which basically says there is an option in the "Tables and Database Designers" section called "Prevent saving changes the require table re-creation" - when this is checked, you will receive errors like you've asked about.

When I disabled this option, I was able to save my database diagram.