Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Management Studio > Table Designer > Set Primary Key > greyed out

    In SQL Server 2008 Management Studio , the menu option

    menu bar > Table Designer > Set Primary Key

    on my system is currently greyed out. It's not greyed out however on 2 other systems we are using.

    Can anyone show me how to configure this option ? I can't find it anywhere for example under menu bar > tools > options

    I believe I found this option once before a long time ago, but no more. I've searched and search elsewhere on the web ot no avail.....

    thanks

    John

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would guess one of two things is wrong here. Either you do not have permissions to alter tables in the environment you are working in or there is already a primary key. I do not use the GUI for much of anything, so please open up a new query window and execute following and it should give you an error indicating which problem you have.
    USE MyDatabase /*MyDatabase to your database name*/
    go
    ALTER TABLE MyTable /*Change MyTable to your table name*/
    ADD CONSTRAINT PK_MyPrimaryKey /*PK_MyPrimaryKey as needed*/ PRIMARY KEY CLUSTERED
    ON MyColumn /*change MyColumn column name as needed*/
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    Many thanks for reply and there's been some success .
    With some experimentation I got the following revised approach to work.....

    USE MyDatabase /*MyDatabase to your database name*/
    go
    ALTER TABLE MyTable /*Change MyTable to your table name*/
    ADD CONSTRAINT PK_MyPrimaryKey /*PK_MyPrimaryKey as needed*/ PRIMARY KEY CLUSTERED (MyColumn) /*change MyColumn column name as needed*/

    I now have :
    - a primary key on MyColumn and this duly shows up in Object Explorer.
    - the ability to remove the PK using Object Explorer > Design > [right click PK col] using the GUI
    - but no ability to add it back in using the GUI, it's greyed out


    The GUI is pretty useful so I'm still keen to progress this is slower time...

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oops sorry about the ON syntax. I am doing this from memory. I have been on hiatus for a few months which is going to probably end this week. I am here gearing up for a couple of serious technical's over the next two days. I have not coded in 4 months.

    Sure coding is a little slower than the GUI the first time, but the code can be saved and reused and you gain a better under standing of what you are doing, and it allows you to deploy to several servers and environments with little or no changes. Many years ago I had to deploy to servers in Hong Kong over VPN. Both remote desktop and the Enterprise Manager were too slow to be of any use and I had to do everything over osql and batch files.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •