Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
8  
Reliance on a GUI will lead you to much more and bigger problems then this. I recommend you become well versed in not only T-SQL DML, but also DDL. –  Thomas Stringer May 11 '12 at 23:59
    
Thanks @Shark, care to explain a bit? –  Emmad Kareem May 12 '12 at 0:23
4  
@Emmad Kareem : Creating tables explicitly using DDL queries gives you much more control ( for example, you can name all table constraints according to your company naming conventions). Also, very important side effect is that you learn syntax faster, and you don't have to rely on particular GUI implementation. –  a1ex07 May 12 '12 at 1:04
    
@Shark, I agree, thanks again. –  Emmad Kareem May 12 '12 at 6:58
2  
The GUI will also do things like drop and recreate a table rather than use alter table which is a big problem when you have a large table. Further, since all code should be in source control, you need to write the scripts for any database changes anyway and it is easier to deploy from scripts where you know exactly which changes belong with which branch of the code than to use a program that looks at the differnces and you have to decide which ones not to push to prod because they are associated with a change that is not finished yet. There is no way I would allow anyone to use the GUI! –  HLGEM May 16 '12 at 19:25
show 1 more comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks Jon for pointing this out. –  Emmad Kareem Jul 10 '12 at 20:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.