将 GUID 主键更改为整数主键 [英] Changing GUID Primary Keys to Integer Primary Keys

问题描述

几个月前我接手了一个应用程序，该应用程序使用 Guids 作为主表上的主键.我们最近遇到了一些与索引相关的数据库问题，我刚刚阅读了使用 Guids 作为主键的内容，我了解到它们可能是一个多么糟糕的主意，我认为可能值得一看在数据库变得太大之前更改它们.

I took over an application a few months ago that used Guids for primary keys on the main tables. We've been having some index related database problems lately and I've just been reading up on the use of Guids as primary keys and I've learnt how much of a bad idea they can be, and I thought it might pay to look into changing them before the database gets too large.

我想知道是否有一种简单的方法可以将它们更改为 Ints?有没有一些很棒的软件可以为我做到这一点?还是全由我决定?

I'm wondering if there's an easy way to change them to Ints? Is there some amazing software that will do this for me? Or is it all up to me?

我正在考虑在所有适当的表中添加一个额外的 Int 列，编写一些代码以基于 CreationDate 列使用 1 - n 填充此列，编写更多代码以填充所有相关表中的列，然后切换与新 int 列的关系.听起来是不是太难了……这是最好的方法吗?

I was thinking of just adding an extra Int column too all appropriate tables, write some code to poplate this column with 1 - n based on the CreationDate column, writing some more code to populate columns in all related tables, then switching the relationships to the new int columns. Does't sound TOO difficult... Would this be the best way to do it?

推荐答案

在结合上述所有链接的片段后，我想出了这个脚本，为了答案而进行了简化.

After combining pieces from all the above links, I came up with this script, simplified for the sake of the answer.

更改前的表格

JOB
Id Guid PK
Name nvarchar
CreationDate datetime

REPORT
Id Guid PK
JobId int
Name nvarchar
CreationDate datetime

脚本

-- Create new Job table with new Id column
select JobId = IDENTITY(INT, 1, 1), Job.*
into Job2
from Job
order by CreationDate


-- Add new JobId column to Report
alter table Report add JobId2 int

-- Populate new JobId column
update Report
set Report.JobId2 = Job2.JobId
from Job2
where Report.JobId = Job2.Id

-- Delete Old Id
ALTER TABLE Job2 DROP COLUMN Id

-- Delete Relationships
ALTER TABLE Report DROP CONSTRAINT [FK_Report_Job]
ALTER TABLE Job DROP CONSTRAINT PK_Job

-- Create Relationships
ALTER TABLE [dbo].[Job2] ADD  CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
([JobId] ASC) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

ALTER TABLE [dbo].[Report]  WITH CHECK ADD  CONSTRAINT [FK_Report_Job] FOREIGN KEY([JobId2])
REFERENCES [dbo].[Job2] ([JobId])
ON DELETE CASCADE
ALTER TABLE [dbo].[Report] CHECK CONSTRAINT [FK_Report_Job]


-- Rename Columns
sp_RENAME 'Report.JobId', 'OldJobId' , 'COLUMN'
sp_RENAME 'Report.JobId2', 'JobId' , 'COLUMN'

-- Rename Tables
sp_rename Job, Job_Old
sp_rename Job2, Job

我创建了 Job2 表，因为这意味着我不必接触原始的 Job 表(除了删除关系)，这样万一出现问题，一切都可以轻松恢复到原始状态.

I created the Job2 table because it means I didn't have to touch the original Job table (apart from deleting the relationships), so that everything could easily be put back to it's original state in case something went bad.

这篇关于将 GUID 主键更改为整数主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！