Modifying a SQL 2008R2 table's Primary Key, table has existing data.

Original table design has 4 columns:

FCode (PK, nvarchar(50), not null)
ID (PK, uniqueidentifier, not null)
Allow (bit, not null)
ICode (nvarchar(50), not null)


The client has asked for a modification where we now need to use FCode and ICode and the primary key and not longer need the ID uniqueidentifier as they want to track this at a higher level than originally thought.  The problem is, I'm assuming this table may have records already in it that will violate this new primary key.

Is there a way to re-insert as much data as possible from the original table, the data that doesn't exist the new primary key.  I'm hoping to save as much existing data as I possibly can.

Thanks,
Jon
JBM2015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
1   In SSMS find the table, and do a right-click > Script Table As > Create To > New Query Editor.
2   In the new query window that contains the above CREATE TABLE script, scroll down to find the current primary key name,  and copy it into your clipboard using ctrl-c
3  Execute the below code, renaming the obvious
ALTER TABLE your_table_name
DROP CONSTRAINT your_current_pk_name
GO

ALTER TABLE your_table_name
ADD CONSTRAINT make_up_a_new_pk_name_here PRIMARY KEY (FCode, ICode) 
GO

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The problem is, I'm assuming this table may have records already in it that will violate this new primary key.
Explain what you intend do to with the offending row(s).  UPDATE with unique values, throw into a temp table, delete, sell on eBay, ...
0
JBM2015Author Commented:
Thanks Jim.

The thought on duplicate records (according to the new primary key) would be to use the ICode column to join to the IMaster table based on ICode.Code = IMaster.Code (many rows) and then figure out which of these rows has the highest version number and use that record to know which record to keep and then any other rows for previous versions can be deleted.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

PortletPaulfreelancerCommented:
not sure what "figure out ... highest version number" requires, but you could do something like this using ROW_NUMBER()
and note that the order by uses DESC

with CTE as (
   select ID, FCode, ICode
   , row_number() over(partition by FCode, ICode order by ID DESC) as rn
   from your_table
)
delete from CTE
where rn > 1;

then alter the PK
0
JBM2015Author Commented:
Thanks Paul,

The ICode field in the table we're discussing here would be used to link to the IMaster table and then within the IMaster table that contain many records per ICode, I would need to find the record in IMaster table that matches this ICode and has the highest value in a Version column within the IMaster table and then use that records identity to know which record to keep in the main table we're discussing here as that identity value is the ID field in the table that has the primary key change and the data cleanup needed.
0
PortletPaulfreelancerCommented:
I was pointing to a method, not trying to write the full query, but it will look a something like this:

with CTE as (
   select yt.ID, yt.FCode, yt.ICode
   , row_number() over(partition by yt.FCode, yt.ICode order by  IMaster.Version DESC) as rn
   from your_table yt
   inner join IMaster on yt.ICode = Imaster.ICode
)
delete from CTE
where rn > 1;

as always with deletions I'd want to test it well before applying it for real, and have a convenient way of restoring the table ready too
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
just a "by the way"

you didn't provide the name of the table in the question, so we have to use something like "your_table"

always makes it easier for all involved if you provide the table name(s)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim

btw if you need to build on Paul's comment on deleting rows check out my article SQL Server Delete Duplicate Rows Solutions
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.