数据库生成永不重复序列号

fanwenhu

已于 2023-08-11 16:34:23 修改

阅读量304

点赞数

分类专栏：数据库序列号文章标签：数据库序列号

于 2019-08-23 15:03:50 首次发布

本文链接：https://blog.youkuaiyun.com/fanwenhu/article/details/100038428

版权

这篇博客介绍了一个在SQL Server中创建的存储过程，用于生成永不重复的序列号，作为表的主键。通过在`SysObject`表中存储表名和主键头，该存储过程可以确保即使在数据库崩溃后，也能正确地生成和分配主键，避免数据冲突。文章还展示了如何创建`SysObject`表和调用存储过程`SysGetObjectPKId`来获取新的序列号。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用的是 mssql 存储过程, 一句指令生成一条,可替换数据库自增列主键,可有效避免数据库崩溃会还后多表主键匹配驴唇不对马嘴的问题.

--创建表
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SysObject](
   [ObjectId] [CHAR](12) NOT NULL,
   [ObjectName] [NVARCHAR](100) NOT NULL,
   [ObjectDescription] [NVARCHAR](200) NULL,
   [ObjectRev] [NVARCHAR](100) NULL,
   [ObjectType] [NVARCHAR](10) NULL,
   [ObjectPKHeader] [CHAR](4) NULL,
   [ObjectPKCounter] [BIGINT] NULL,
   [CreateDate] [DATETIME] NULL,
   [Protected] [BIT] NULL,
CONSTRAINT [PK_O_DbMaint] PRIMARY KEY CLUSTERED
(
   [ObjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_SysObject] UNIQUE NONCLUSTERED
(
   [ObjectName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_SysObject_1] UNIQUE NONCLUSTERED
(
   [ObjectPKHeader] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表对象ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysObject', @level2type=N'COLUMN',@level2name=N'ObjectId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表对象名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysObject', @level2type=N'COLUMN',@level2name=N'ObjectName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'描述' , @level0type=N'SCHEMA',