Is it safe to drop and then create the constraints in the database

Question

Hi All, I am a bit confused and need your help. I have a database that has more than 150 tables. I have several fields which should support Unicode but right now they dont and therefore I want to change the column datatype to ntext and nvarchar in this process I have to drop the constraints and then create them again. Is it safe to drop the constraints and create them again.

*I have 286 columns to change and just dont know many constraints have to be dropped and created in this process.

*All I want to know is that is it safe to alter the column data type of a fully grown project.

Answer 1

If you can do this without the database being live then it should be safe. Important things to bear in mind are making sure that you have all the constraints documented, that none of them will be affected by the change in column type (or if they are that you have mitigated for that) and that you are clear on the dependencies between those constraints.

I guess I'm just saying, make sure you have a clear process and that you follow it.

Answer 2

But there is a better way: you can disable the the Index. See on msdn. This keeps the index definition.

If you run this on a customers machine (eg. with an installer) you should put the database in single user mode to make sure that no application is inserting data in the meanwhile. This would allow inconsistent data and you wouldn't be able to create / enable the index.

Edit: (after comment by gbn)

Disabling does most probably not work for your case.

We are also removing constraints when upgrading the database, and in general it is save. Others aspects are already mentioned by others: you need to know them all to create them. There are tools that create scripts from existing databases, or you can write it yourself. All the information should actually be in the database. Eg you get the scripts by the management studio, but only one by one (as i know).

If you do this in a productive environment, you can put the database in single user mode to ensure that no application tries to use it.

Answer 3

All this can be done transactionally (all complete or all rollback) with 3rd party comparisons tools.

That is, you can do the changes individually in development (using SSMS to make changes) but generate "safe" change and rollback scripts (but always have a backup though).

Otherwise, it's safe to do: the problem may occur if someone is using the database at the time and either put in wrong data or try a query with no index.

The 3rd-party-tool-uses-transaction approach locks the objects for the duration of the entire changes.

Of course, you could do one at a time rather that big bang but these tools are still useful.

Answer 4

we make schema changes all the time, here is our procedure in general:

for each table:

• make your changes within SQL Server Management Studio, you will notice that these scripts drop and recreate constraints all the time with no problems

• generate the scripts, copy them to a file

• cancel the changes (don't apply them to the database)

go to a test/development database with the exact same schema as production and run your script

if you have any errors, resolve them, restore test/development and retest

if you have no errors

• make a backup as necessary

• schedule the applicaion to be down with the users if necessary

• put the database in single user mode, this will prevent any data from being changed when the constraints are missing

• run the script file

• take the database out of single user mode

Answer 5

Instead of dropping and recreating constraints you may try the approach of disabling and enabling them. I am not sure though if this would work if you change the column type meanwhile, but as long as you keep the column names unchanged it is worth testing