SQL Server add primary key

Question

I have a table that needs to be given a new primary key, as my predecesor used a varchar(8) row as the primary key, and we are having problems with it now. I know how to add the primary key, but am not sure of the correct way to add this new primary key to other tables that have the foreign key. Here is what I have:

users table:
  old_user_id    varchar(8)
  ...
  ...
  new_user_id    int

orders table:
  order_id       int
  ...
  ...
  old_user_fk     varchar(8)
  new_user_fk     int(11)

I need to get the same results whether I join the tables on users.old_user_id=orders.old_user_fk or users.new_user_id=orders.new_user_fk. Any help is appreciated.

Answer 1

What is int(11)? SQL Server only has tinyint, smallint, int and bigint

I would suggest you add the new columns to all the tables then update the values so that they match....run a couple of queries to make sure it all works. drop the PK and FK constraints and add new PK and FK constraints using the new columns

of course I would back up all these tables just in case

select * into backup_ orders from orders

This way you always have that data in case you need to roll back

Answer 2

Replace your varchar with int, do not keep duplicates in the same table.

Write some TSQL similar this code I used recently:

BEGIN TRANSACTION

-- temp tables to hold data
DECLARE @HeaderTemp table 
       (vid varchar(8),
        [Name] varchar (50) )

DECLARE @SecondaryTemp table 
   (vid_fk varchar(8),
    [Value] varchar (50) )

-- store table data        
INSERT INTO @HeaderTemp
   SELECT * FROM [Header]

INSERT INTO @SecondaryTemp 
   SELECT * FROM [Secondary]

-- empty data from tables
DELETE FROM [Secondary]   
DELETE FROM [Header]   

-- drop constraints
ALTER TABLE [SECONDARY] DROP CONSTRAINT [FK_SECONDARY_HEADER]
ALTER TABLE [dbo].[HEADER] DROP CONSTRAINT [PK_HEADER]

-- convert varchar to int
ALTER TABLE [SECONDARY] ALTER COLUMN VID_FK INT NOT NULL
ALTER TABLE [HEADER] ALTER COLUMN VID INT NOT NULL

-- re-create constraints
ALTER TABLE [dbo].[HEADER] ADD  CONSTRAINT [PK_HEADER] PRIMARY KEY CLUSTERED 
(
[vid] ASC
)     
ALTER TABLE [dbo].[SECONDARY]  WITH CHECK ADD CONSTRAINT [FK_SECONDARY_HEADER] 
  FOREIGN KEY([vid_fk]) REFERENCES [dbo].[HEADER] ([vid])

-- put data back
INSERT INTO [Header]
   SELECT * FROM @HeaderTemp

INSERT INTO [Secondary]
   SELECT * FROM @SecondaryTemp 

COMMIT TRANSACTION