Be sure to update code that inserts into Addressesįor a more detailed explanation of this 'no maintenance window' approach, please see the article I wrote about it on SQL Server Central (free registration required): Restructure 100 Million Row (or more) Tables in Seconds.make sure no data in can change (this is very temporary).Build copies of both tables and with the desired structure (but no FK).If there is no maintenance window then you have to not only deal with contention on the table (and adding / removing fields requires a SCHEMA lock), but you also need to update the process that adds data to the If there is data in these tables (and you do not have a maintenance window): After you are sure that all data is there and matches, then you can drop the field in both the and tables.Update Stored Procedure(s) or whatever processes/code insert (and possibly update if the StateID field can change) into the table ZZZZZ occurs when you create an output table using the SELECT syntax in the SQL procedure A warning similar to the following occurs if you create an.Update the table to match the new IDs in the table UPDATE addr ALTER TABLE ADD INT NOT NULL CONSTRAINT DEFAULT (0).If there is data in these tables (and you have a maintenance window): Recreate Foreign Key on that references.ALTER TABLE ALTER COLUMN INT NOT NULL.ALTER TABLE ADD INT IDENTITY(1, 1) NOT NULL.If there is no data in these tables (obviously much easier): You can't even change a datatype if the field is used in an index. You cannot change datatypes of Key fields (Primary or Foreign).

Sql alter table add primary key