Alter to dababase design

Discussion in 'Databases' started by saykaof, May 10, 2010.

  1. Here is the message:
    "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be recreated or enable the option Prevent saving changes that require the table to be re-created."

    Using Microsoft SQL server management studio express:
    Problem is I can not save my change.

    What I was trying to do is to add a primary key to my table called testdb1 (It is a new and empty table with five columns). After I finished adding the primary key and the increament amount, I click the save button and then above message show up.

    Any idea where can I make the change so it would allow me to make my change?

    FS
     
  2. Ray

    Ray

    The error message indicates you have kind of relationship to a different table that is preventing you to drop the table completely.

    As a test create a new table and just set it up with two columns. Name it fname and lname. Then save it. Log out then log back in and on that table set fname as a primary key.
     
  3. Ray,

    I did as you suggested. I stil have the same problem.
    Let me go over what I did:

    From SQL server management studio:
    I selected new table
    type in fname, varchar type (Note: the other table is int type)
    type in sname, varchar type
    save, name test_2
    close window

    right click on test_2 node
    select design
    make change
    save, then the same message show up.

    Note: is it matter if there is no "alter to" option available, I think it should be.

    Please suggest another remedy for my problem.

    FS
     
  4. Ray,

    I used server explorer to make the change (add PK), and the server allowed. I donot why sql server management studio could not.

    FS
     
  5. Ray

    Ray

    That's odd. I can only assume that your SSMS may have another connection opened to it that is locking the object, but then again I've never seen that before.
    Try shutting down and starting backup your computer. Hopefully that clears the connection. Then connect to the SQL server again using SSMS.
     
  6. In Server Management Studio click on tools -> options -> Designers -> turn off option "Prevent saving changes that requires table re-creation"

    Yeah not sure why that option defaults to on.
     
  7. Ray

    Ray

    I forgot about this setting. This setting is actually quite useful to sustain database integrity. If you are new to SQL or are not sure of the structure of the database I do suggest that you keep this on. But you are free to disable it if needed.
     

Share This Page