Post reply

Best practices to add Primary key to existing tables

  • March 15, 2016 at 12:17 am

    #309111

    Any solution to add primary key to the existing tables with contains around 1000 rows(int,not null), i want to add PK for all 1000 rows which existing already

  • March 15, 2016 at 6:06 am

    #1865182

    here's a very basic example: just add a new identity column as a primary key, assuming there is not already any identity and not any existing PK either.

    the identity function will populate the values for existing rows.

    i prefer to have a {tablename}ID as the Identity column name, it makes foreign keys very clear what they belong to later;

    having 1000 tables, all with a column named "ID" is just a recipe for confusion.

    ,

    CREATE TABLE Example(ExampleText varchar(30),CreatedDate datetime default getdate() )

    insert into Example(ExampleText) VALUES('Florida'),('Ohio'),('Massachusetts')

    ALTER TABLE Example ADD ExampleID int identity(1,1) NOT NULL PRIMARY KEY

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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