In my class this week, the students went through the inserts, updates and deletes that they had created for their database design, and then the moment of truth – creating the queries that answer the questions from the requirements.
It’s often at this stage when you realize that the design isn’t exactly what you want. Perhaps you missed a JOIN condition, maybe you don’t have the Declarative Referential Integrity that you need. In a couple of cases in the class, the students had picked a Primary Key type or value that they now wanted to change.
I’ll break up this process into two posts – this one will cover how you do that if the tables don’t contain any data and the other will cover the more difficult situation when you have data (that you need to keep) and you want to change keys. In both of these posts I won’t cover the specific steps (because in every situation, you know, it depends) but I will explain the broad outline you need to do the job.
Let’s start with the first instance – you have no data in the tables that you need to keep. It isn’t as simple as just opening the Database or Table Designer in SQL Server Management Studio (SSMS) and changing the data type of the Primary Key (PK). If you have other tables that depend on that key using a Foreign Key (FK) you’ll get a constraint violation. If you try to change the FK first you’ll get the same thing – the PK in the “Parent” or referenced table and the FK in the “Child” or referencing table have to stay in synch.
So what you have to do is to “relax” or drop the constraints, in order. You can’t drop the PK if an FK is pointing to it, so you need to find and drop the FK’s, and then the PK’s. There are lots of scripts on the Web to do that. Here’s one: http://www.mssqltips.com/tip.asp?tip=1376 – and there are several others. As always, don’t just apply these blindly. Read and understand what a script does before you run it.
With the keys dropped, you can now make your changes to the PK field or even which field will become the PK – just remember that there are corresponding changes to the FK that you have to make so that they stay in synch.
Even with the keys dropped, you can’t always change one data type to another – sometimes this is a database drop-and-create activity. If you have to go that route, make sure you’ve documented your security carefully, and recreate and logins and object permissions after you recreate the new database.
Once the changes are made, re-apply your constraints with the appropriate changes.
Which brings up another very important process that I teach. I always recommend that you script out the database for each change during the design process. And in the design process, I keep two kinds of scripts: One that has all of the ALTER statements so that I can start with the original design and move forward one step at a time, and another, monolithic script that would re-create the database as it currently stands.
In that second script, I also use a special format. I have the tables first, then the constraints, then the programming objects (such as views, stored procedures, triggers and so on) and then the security. I’ve learned over time that it’s easier to make the changes to tables when the script is in this format.
I’ll end with this – This entire process is why I harp so much on the Entity Relationship Diagram (ERD). Not just making one, or going through the exercise. If you carefully created, study, and review the ERD with your developers, other DBA’s and Business Analysts, you can avoid costly changes later. In fact, that’s it’s entire purpose.