Add Primary Key to Existing Table

  • I need to add a primary key to an existing table of 30000 rows. Is there no way to do this without using stored procedures? I tried using the ROW_COUNT function with no luck.

    THanks!

  • ALTER TABLE [tableName]

    ADD CONSTRAINT [constraintName]

    PRIMARY KEY ([columnName])

    make sure the field is not nullable and there are no duplicates

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • Sorry, Ive added the field, but how do I populate it with autoincremented values?

    Thanks!

  • ALTER TABLE [tableName]

    ADD [columnName] int identity(seed,increment)

    This will populate the new field with an auto-incrementing number

    Then you can add the primary key constraint on the new field

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • And make sure you do adequate testing prior to releasing to production.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • I tried this code but recieved the following:

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near 'seed'.

  • seed is the starting number.

    increment is the amount you want to increment for each record insert.

    ALTER TABLE [tableName]

    ADD [columnName] int identity(1,1)

    will yield

    1,2,3,4,5...

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • That worked perfectly. Sorry for my lack of experience and thank you again for your help and patience!

  • I have used this code since I first began, and after a few years I have not changed it yet. I recommend keeping it on hand...after a while it will be put to memory to the point you have to think hard to remember to change the standard field name:

    Alter Table dbo.[TableName]

    Add ID_Field Int

    Identity(1,1)

    Primary Key

    And yes, I call the field ID_Field. I changed that one part recently because my new company did not get the Oid_Record field title (OriginalID_Record)...other than that I have not changed this script in years.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply