I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is being referenced by many foreign keys in other tables. So, what are the strategies adopted for droping a table referenced by many foreign keys from other tables ?
Thank you for help in advance 🙂
Best Answer
Assuming the column you want to change is the one the FKs point to, you need to drop all of the foreign key constraints - it's not enough to simply disable them. You can't drop the table, anyway; and you can't drop the column if it's actually referenced, and the only way to add the
IDENTITY
property is to drop/re-create the column, or drop/re-create the table.Here is a script that generates the commands to drop and later re-create all of the foreign keys that point at
dbo.yourtable
. It is based on this answer. Drop is easy, just build a simple concatenated list fromsys.foreign_keys
:Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column:
When you're happy with the output (keep in mind that
PRINT
is limited to 8K, so it may look like the command is truncated), add this to the end and run it again:This should be a pretty simple exercise because, based on information you've added later, these tables are empty.
If this is not the column the FKs point to, you may be able to drop just the column and re-add it with the
IDENTITY
property. But if you had data, this would mean you would have to accept whatever arbitrary row numbers SQL Server would assign, or be prepared to completely gut the table (and all the FK tables, or at least the FK column(s) pointing to this table) and re-populate. You would also have to accept that the column may now be in a different "physical" location within the table (you shouldn't care about this at all, but I mention it because many do for reasons unknown).In SQL Server 2012, as a workaround, you could implement a
SEQUENCE
instead of using theIDENTITY
property. Since you're only on SQL Server 2008, you could instead generate the increment using your own serializableMAX+1
construct. But that's a different question altogether. (I just ran a search, I have not looked at how good those answers are; also see some ideas here.) Or move to a more modern version with more options.