Why does Management Studio not automatically create FK column in the parent table?

Question

When creating a FK in SQL Server 2008 Management Studio using a single column, one has to first create a column manually then select the PF/FK relationship from a dialog box.

I am wondering why is there no option to let the software automatically create the column with the correct data type in the parent table as soon as the drag operation completes?

Answer 1

The comments on the question explain some of the perils of using Management Studio to make schema changes, so I won't get into that.

What I want to show is how to accomplish what you're asking using T-SQL:

ALTER TABLE [dbo].[MyTable]
    ADD NewColumn int NOT NULL
        CONSTRAINT FK_MyTable_MyOtherTable
            FOREIGN KEY REFERENCES [dbo].[MyOtherTable](Id);

The syntax is extremely powerful as you can create multiple constraints (DEFAULT, PRIMARY KEY, etc.) on the same column using this syntax (even named constraints as I demonstrated above). The best part is that it's an atomic operation, so if any part of it fails, the whole thing rolls back automatically.

See the ALTER TABLE documentation on MSDN for more information.