Moving primary key from int to Guid

Question

In my current project, I have to sync multiple client databases to one server database. I had given a try with Microsoft's sync framework and is not useful in my case. Therefor I have to do sync manually. for that I need to change primary key constraints from int to guid in all the tables.

Problem is this primary keys are also referred as foreign key in other tables. I had referred below,

Moving from ints to GUIDs as primary keys

I am not good at SQL. I understood the logic but actual implementation is very tough for me.

ALTER TABLE MyTable 
ADD GuidCol NVARCHAR(50)  NOT NULL,
CONSTRAINT AK_GuidCol UNIQUE(GuidCol)

in above code I tried to add a column GuidCol as unique column after googling a lot. But I don't know How can I fill values in the newly created column? How can I make this column as PK after assigning value(s)? How to make sure that this process won't break existing foreign key constraints?

What would a manual sync have to do with the type of the foreign keys? You may be trying to address the wrong issue. — Gordon Linoff
There is no use of foreign keys in manual sync. But here I'm trying add new unique GUID column. I want this column to be PK so for that I need to update all the foreign key tables. For example. if PK is 1 , Guid is G then I need to change the entry in other table where FK is 1 to G. Sorry for bad write up. I hope you'll get what I want to say. — sachin
Does this answer your question? Moving from ints to GUIDs as primary keys — Jim G.

sepupic sepupic · Accepted Answer · 2017-09-13T11:25:26

I don't know How can I fill values in the newly created column?

The link you provided gives a solution:

Create a new column for the guid value in the master table. Use the uniqueidentifier data type, make it not null with a newid() default so all existing rows will be populated.

So your code should look like this:

alter table myTable add GuidCol uniqueidentifier not null default newid()

How can I make this column as PK after assigning value(s)? How to make sure that this process won't break existing foreign key constraints?

The same answer tells you:

Create new uniqueidentifier columns in the child tables.
Run update statements to build the guild relationships using the exisitng int relationships to reference the entities.
Drop the original int columns.

Use the same uniqueidentifier type as in previuos statement, but instead of providing defalut, run the update statements joining the tables on existing integer id and set in the child's column the corresponding parent value. Now you have child-parent relationship established but without constraints on them. Drop int columns and create PK and FK on new guid columns

Moving primary key from int to Guid

1 Answers