Professional Web Applications Themes

How to add a constraint - Microsoft SQL / MS SQL Server

Hi All, I read and read the SQL reference book on ALTER TABLE but could not figure out how to add a PRIMARY KEY constraint to a column. For example, this one will fail ALTER TABLE X ALTER COLUMN ZZZZ ADD CONSTRAINT [PK_X] PRIMARY KEY NONCLUSTERED. Basically, I want to make a column primary key.......

  1. #1

    Default How to add a constraint


    Hi All,

    I read and read the SQL reference book on ALTER TABLE but could not figure
    out how to add a PRIMARY KEY constraint to a column. For example, this one
    will fail

    ALTER TABLE X
    ALTER COLUMN ZZZZ
    ADD CONSTRAINT [PK_X] PRIMARY KEY NONCLUSTERED.

    Basically, I want to make a column primary key....



    David Guest

  2. #2

    Default Re: How to add a constraint

    David,

    Assuming ZZZZ is defined as NOT NULL..

    ALTER TABLE X
    ADD CONSTRAINT [PK_X] PRIMARY KEY NONCLUSTERED (ZZZZ)

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    " David N" <com> wrote in message
    news:%phx.gbl... 
    one 


    Dinesh.T.K Guest

  3. #3

    Default Re: How to add a constraint

    alter table X
    add constraint pk_X primary key nonclustered (ZZZZ)

    -Sue

    On Fri, 8 Aug 2003 09:44:25 -0700, " David N"
    <com> wrote:
     

    Sue Guest

  4. #4

    Default Re: How to add a constraint

    if you look closely at the BOL syntax for ALTER TABLE, you'll see that ALTER
    COLUMN doesn't support using alter column to add a PK constraint. However,
    you can add a table level PK constraint for the column using syntax like
    this,



    create table TestMe (col1 int not null)
    go
    alter table TestMe add CONSTRAINT NewKeyName primary key (col1)
    go

    --

    Brian

    " David N" <com> wrote in message
    news:%phx.gbl... 
    one 


    Brian Guest

  5. #5

    Default Re: How to add a constraint


    The

    ALTER TABLE X
    ADD CONSTRAINT [PK_X] PRIMARY KEY NONCLUSTERED (ZZZZ)

    works beautifully.

    Thanks All for helping.



    " David N" <com> wrote in message
    news:%phx.gbl... 
    one 


    David Guest

Similar Threads

  1. Adding Constraint
    By wallace reis in forum MySQL
    Replies: 2
    Last Post: December 15th, 07:27 PM
  2. Drop constraint in PLSQL
    By MaisonBorniol in forum Oracle Server
    Replies: 5
    Last Post: November 6th, 03:29 PM
  3. Help Please :- Group Constraint
    By Word of Mouth Productions in forum Macromedia Director Lingo
    Replies: 1
    Last Post: August 24th, 02:36 PM
  4. violation constraint
    By marcos in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 8th, 02:01 PM
  5. Conditional Constraint?
    By Howard J. Rogers in forum Oracle Server
    Replies: 9
    Last Post: December 24th, 02:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139