Adding Primary key column to existing table

  • I have a table with 10000 records. The table does not have any primary key constraint.

    Now i want to add a primary column.How can i add a column with primary key constraint to the existing table? Can somebody provide the query to alter the column

  • Hi.

    It would go something like this.

    Step 1

    First, add your new column.

    Alter table TableName add NewCol int default(0) not null

    I think that you must have a not nullable column. If I remember correctly, MSSql Server doesn;t like making primary keys from nullable columns.

    You can accomodate this in an alter table by adding a default constraint then declare it as not null.

    Step 2. Load the values into the new column. since the table had 1000 rows in it, now those are all set to the value of the default constraint. Since you are ablout to make this column a primary key, there must be unique values for each row in the new column.

    Step 3. Create the primary key constraint

    alter table TableName add constraint New_primary_key primary key (NewCol)

    Optionally, you could remove the default constraint on the new column.

  • Using SQL Server 10.0.2531 Management Studio, I was able to skip the updating values part.

    So,

    1. Design table, Add

    .[Id] column as Identity seed 1 increment 1, save.

    2. Right click [Id] column, set as primary key, save. Done.

    SQL server added the incrementing values automatically! Awesome product.


    {Francisco}

  • Pls refer this link:

    http://msdn.microsoft.com/en-us/library/ms190273.aspx

  • You may use code like this:

    ALTER TABLE [TableName] WITH NOCHECK

    ADD CONSTRAINT [PrimaryKeyName] PRIMARY KEY CLUSTERED ([ExistingColumn])

    WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

  • Go to Sql Server Mgmt Studio.

    Navigate to the table.

    Right click --> Edit/Modify Table

    DO NOT SAVE AFTER DOING BELOW CHANGES

    Add New Column.

    [ select identity,primary key, foreign key, etc....]

    Right Click newly created column --> select Generate Change Script

    Done.

    A new dialogue box will appear goving you the script for all the changes done above.

    Copy the script and use as required.

    Verify the script provided by Microsoft and do necessary changes.

Viewing 6 posts - 1 through 5 (of 5 total)

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