0

I followed the steps listed here

Auto increment primary key in SQL Server Management Studio 2012

to create an auto increment query. After doing so, should I delete the original primary key I had created?

Community
  • 1
  • 1
Skyler Nakashima
  • 1
  • 2
  • Could you elaborate on what you are trying to accomplish? Why do you want to delete your PK? – Chris Stillwell Nov 20 '15 at 16:41
  • Not sure what the question is asking. What original primary key are you talking about? Whenever you insert a row into the table, if the primary key column is set to identity/auto increment then it will automatically create the value of the primary key for you. – Sean Cox Nov 20 '15 at 16:43
  • @TheShaman it is not required to be the primary key. You can set the identity property to a column that isn't the primary key and the same functionality will happen. – Sean Lange Nov 20 '15 at 16:46
  • I think what they are trying to say is that they originally didn't use auto increment and have implemented after values have already been inserted into the table. I see no reason why you would need to delete the original values. – Dane Nov 20 '15 at 16:48
  • @SeanLange I know this, but the question clearly specifies primary key and references another page talking about primary keys. I would say the most common use of this feature is to create primary keys though. – Sean Cox Nov 20 '15 at 16:53
  • @TheShaman I know they stated primary key but I wanted to be very clear that the increment is in no way related to the column being the primary key and your original comment was not clear on that. :) – Sean Lange Nov 20 '15 at 17:18
  • I did not originally use auto increment and did implement it after values were already inserted into the table. After implementing it, I now have 2 columns with the same values in them due to the auto increment column being added. My question is, is it good practice to leave it as is or to remove the non auto increment column? – Skyler Nakashima Nov 20 '15 at 17:58

1 Answers1

0

Not sure what original primary key you are talking about. Lets say you have a two column table named names. One column is the name_id, set to IDENTITY, and the other is a name to store a username:

[name_id|username]

Whenever you insert a value into the table, the name_id will automatically be created because it is an IDENTITY column. You do not have to specify it, in fact, you cannot specify it. So, the INSERT statement would look like:

INSERT INTO names VALUES('Bob')

If you then look at the table, the name_id will get an automatic ID:

[name_id|username]
[1      |Bob     ]

The next INSERT you do, the name_id will increment the ID by 1 (considering you left the default auto-increment value):

INSERT INTO names VALUES('Carl')

You then get : 

[name_id|username]
[1      |Bob     ]
[2      |Carl    ]
Sean Cox
  • 782
  • 1
  • 5
  • 12
  • What if you also had a column in there before adding the name_ID column that held the same values but won't automatically increment when inserting values. I.e., you also had a third column 'usernumber' with values of 1 and 2 for Bob and Carl, respectively. When you insert a new name, Frank, the name_ID automatically adds a 3 but the usernumber column doesnt. Should usernumber column be removed? – Skyler Nakashima Nov 20 '15 at 18:01
  • So, from what I understand you already have a column setup as a primary key which is not set up as an IDENTITY column? You are wanting to make it an IDENTITY column but already have values in it. Are the values unique? What is the highest value? – Sean Cox Nov 20 '15 at 18:20