Add a column with a default and foreign key constraint

Every so often you may be presented with the requirement to add an additional column to one of your database tables. My experience, with SQL Server 2008 and above at least, is that Management Studio will not allow you to modify table structures via the GUI (backed up here and here) unless explicitly allowed via SSMS configuration options.

This is a good thing. It is turned off by default for a reason.

If you do not possess the skills to be able to perform structure changes via SQL, I wouldn’t want you in Management Studio in the first place.

Regardless, here is a quick snippet to add an additional currency column to a table with a default value of 1 and an FK constraint against the dbo.currencies table.

ALTER TABLE [dbo].[mytable]
ADD [currency_id] INT
CONSTRAINT [DF_mytable_currency_id] DEFAULT ((1)) NOT NULL,
CONSTRAINT [FK_mytable_currency_id] FOREIGN KEY ([currency_id]) 
  REFERENCES [dbo].[currencies] ([currency_id])

Enjoy!