SQL Server 2008: When can I NOT modify table structure? When can I not delete a row?

Question

A couple of summers ago I worked for company that needed an Access DB designed for their sales and record keeping. One thing that bugged me during that summer was sometimes not being able to change the datatype of a column, delete a column, or delete a row (entry). And, if memory serves me (this was five years ago), I think I sometimes copied entire tables of data out, recreated the table with the change I wanted, and then copied it back in. Honestly, probably most of the problems I had had to do with my extreme lack of DB design knowledge at the time, and I was probably trying to change things that you obviously shouldn't. However, I'm trying to avoid the same problems this time around.

This summer, I'm now working in MS SQL Server 2008 Developer, and once again am setting out to design a significant DB system. So as I go about this, my question is, what will I not be able to go back and change later? Specifically, I know that most of the restrictions happen regarding primary/foreign keys, so what should I look out for as I go about designing?

For instance:

1. Can I change the datatype of a primary key? What if it's referenced in another table?
2. Can I change the primary key? And if it's referenced in another table?

What other obvious restrictions might I run into that I should be aware of? Sorry if this seems like a obvious/stupid question, but I think many beginners need to know the answer to this - and I'm one of them!

Thanks!

Answer 1

If the table is not referenced by a foreign key, then go a head, change the primary key. Also, if you're in the the design phase and the tables are empty, you can often change the FK and PK relationships quite flexibly because empty tables don't have any rows to check for compliance.

Also keep in mind that the data type of the PK and the FK must match exactly (i.e. bigint to bigint, smallint to smallint, not bigint to smallint)

1- Changing the primary key of a table is a non-trivial task unless you are using a tool. If you use enterprise manager and change the key, then click to show the script, you'll see all contraints get dropped, target table gets backed up, then dropped a new table created and the everything is rebuilt. If the data in the old primary key doesn't convert, or isn't subsequently unique, hopefully everything rolls back smoothly.

2- Same practical results

When can't you delete rows? You can't delete from the PK table until all the corresponding rows in the FK tables are modified or deleted. To delete Texas from the states table, you will have to update all the order records to the new valid state name, or delete them.

Answer 2

With SQL 2008 Management Studio you can enable an option in Tools -> Options that will block you from saving any changes that require a rebuild of the table. Then you can create a database and try a bunch of operations and see which work and which don't.

I am not a SQL expert so I don't know all of the cases that require a rebuild of the table but I do know that re-ordering columns (including adding a new column anywhere other than to the end of the table) requires a rebuild.

Edit:

Two other things I know of that become a huge pain are UDT's and UDF's that are "schema bound". You can't change those things when other objects are referencing them.