easy button for adding column

Question

Hi All,

I right click on my table in ssms 2008 and select Script Table as / Drop and Create Table to new window and I try to run the script but get an error:

Could not drop table because it is referenced by a foreign key constraint

What was the point of the Drop and Create generate script then?

Thanks,

rod.

Answer 1

The easiest way to add a column to an existing table? Write the ALTER TABLE statement yourself instead of relying on SQL Server Management Studio to do it for you:

ALTER TABLE YourTableName
ADD ColumnName int

Answer 2

The point of the Drop and Create generate script is exactly what you'd think - it gives you an easy way to script out dropping and re-creating a table. However you can't drop a table if other tables reference it via foreign key constraints, which is why you're getting the error message.

If you're just trying to add a column, you can right-click the table in Enterprise Manager and click Modify and just add the column in design view. There's no need to drop the table just to add a column. (And it's especially an awful approach if the table has data in it.)