0

I have this product table and I have this table called manufacturer which I want to delete the table and create it from new, so that I can add identity and autoincrement to it. But since this table is linked in the product table, it does not let me delete the table. Then I tried to delete the product table and then this table is linked to around 10 tables which will be a nightmare to delete all these tables to do one small thing. can somebody help me to add auto-increment and identity to this manufacturer table.

ZAJ
  • 793
  • 3
  • 23
  • 50
  • Possible duplicate of http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table – Ullas Jul 20 '14 at 06:10
  • I get this error when I try to run this query alter table manufacturer add id bigint identity(1,1) Column names in each table must be unique. Column name 'id' in table 'manufacturer' is specified more than once. – ZAJ Jul 20 '14 at 06:16
  • 1
    `alter table manufacturer add id_new bigint identity(1,1)` – vhadalgi Jul 20 '14 at 06:24

1 Answers1

2

You can do this without deleting the table, using an ALTER TABLE statement with the same syntax as you'd use on a CREATE TABLE:

ALTER TABLE [table_name] ADD [ID] INT IDENTITY(1,1)

That will also fill in auto-incremented values for all existing rows.

Matthew Haugen
  • 12,916
  • 5
  • 38
  • 54