Post reply

Adding a Primary Key attribute in SQL 2005

  • August 13, 2007 at 1:23 pm

    #174786

    I have read that in SQL 2005 you can add indexes to fields without locking the table.

    I need to add a primary key constraint on a field so I can set-up transaction log replication.

    Does the table lock when adding the primary key constraint?

    How about if I add a new column and make the new column the primary key?

    Thanks in advance.

    ______________________________
    AJ Mendo | @SQLAJ

  • August 13, 2007 at 1:33 pm

    #726596

    I can't see how it won't lock... the constraint will have to validate all the data somehow.

     

    Maybe one workaround would be to add a unique column (like identity), add the unique constraint / PK on that column using WITH NOCHECK.

     

    However that leaves the constraint untrusted (not a big deal in this case assuming it doesn't interfere with the replication setup).

  • August 13, 2007 at 2:23 pm

    #726609

    How about creating a new temporary table with the proper PK constraint then load it from the production table. When it gets loaded simply rename the production one to another name then rename the newly created temporary table to the production table?

    Kurt

    DBA

    RHWI, Inc

    Poughkeepsie, NY

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

Viewing 3 posts - 1 through 2 (of 2 total)

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