When you're modifying your database, you might get an error that says Saving Changes is Not Permitted. How do you fix this error?

The full error might read:

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created.

This is normally seen with a SQL Server database.

I get this most often when I am trying to alter the details of a field. For example I might realize that 100 characters is just too small for a character field and want to increase that to 150 characters. When I try to make that change, I get the Saving Changes is Not Permitted error.

Here's how to fix it.

First, in the top menu bar of the SQL Server administrator, click on the TOOLS option.

From the TOOLS list, click on OPTIONS.

Now you'll get a list of options you can work with. Look for the DESIGNERS option and expand that so you see all the sub-menu items beneath it.

Now you want to look for TABLE AND DATABASE DESIGNERS.

You're nearly there! The last step is to look for the checkbox that goes with PRESENT SAVING CHANGES THAT REQUIRE THE TABLE TO BE RECREATED.

Make sure that is NOT checked, and then save.

What this is saying is that the database system will allow situations where the table has to be built fresh. In essence this is what is going to happen. Let's say you want to increase the field CompanyAddress from 100 characters to 200 characters because you realize that some addresses you need to store go past 100 characters. This is a change that is going to require the table to be wholly rebuilt, so each row has that new, longer length as part of it. The database will create a temporary table and move all the existing data into it. Then it will drop the current table. It will create a fresh, new table in that space with the same name and with the new, updated field definition. Then it will move all the data into that new table from the temporary table. Finally, it will delete the temporary table.

Let me know if you have any questions!