Menu
Why do I get message that the table needs to dropped and re-created when I add/move columns? I believe this happens after adding foreign key constraints.
What can I do to add new columns without dropping table?
626
You can add a column without dropping the table. If you want the column NOT NULL then you'll have to make it accept NULL first, then set the values through an update, and lastly alter the column to NOT NULL .
Use SQL Server Management StudioIn Object Explorer, right-click the table to which you want to add columns and choose Design. Select the first blank cell in the Column Name column. Type the column name in the cell. The column name is a required value.
Using SQL Server Management StudioIn Object Explorer, right-click the table with columns you want to reorder and select Design. Select the box to the left of the column name that you want to reorder. Drag the column to another location within the table.
If you're more interested in simply getting SSMS to stop nagging, you can uncheck the "Prevent saving changes that require table re-creation" setting in Options->Designers->Table And Database Designers. The table(s) will still be dropped and re-created, but at least SSMS won't pester you quite as much about it.
(This assumes you're working in an dev/test environment or in a production environment where a brief lapse in the existence of the table won't screw anything up)
126
Because that's how SQL Server Management Studio does it (sometimes)!
Use TSQL's ALTER TABLE instead:
ALTER TABLE
ADD myCol int NOT NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
