使用 uniqueidentifier 作为主键并使用参数化查询从客户端应用程序插入它
我创建了一个数据库，其中使用 uniqueidentifier 作为主键。我正在尝试运行一个参数化查询，最终看起来像这样。 (insert into someTable (uniqueID) Values('76c14693-5475-4224-ba94-7d30c919ac59') (uniqueID 是我的 PK)。当我在 SQL Server Management Studio 中运行此插入语句时，它执行时没有问题，但是，当我运行它时，它会失败尝试从我的代码中运行它。
错误：当 IDENTITY_INSERT 设置为 OFF 时，无法在表“someTable”中插入显式值
我已经查过这一点，并且有很多关于打开 IDENTITY_INSERT 的建议，但它们都使用我需要能够使用参数化查询来完成此操作。或者我需要一种方法来获取数据库中新创建的 guid。PS
： 这似乎是每个人都需要的。我需要 ID，因为我需要使用该 ID 将记录添加到其他表
编辑：
这是相关表的结构。
CREATE TABLE [dbo].[someTable]
(
[NewsID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_someTable_NewsID] DEFAULT (newid()),
[ShortTitle] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCreated] [datetime] NULL CONSTRAINT [DF_someTable_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_cmc_News_1] PRIMARY KEY CLUSTERED (
[NewsID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF
) ON [PRIMARY]
) ON [PRIMARY]
I have created a database where I use uniqueidentifier as the primary key. I'm trying to run a parametrized query that in the end looks like this. (insert into someTable (uniqueID) Values('76c14693-5475-4224-ba94-7d30c919ac59') (uniqueID is my PK). This insert statement executes without issues when I run it in SQL Server Management Studio, however, it fails when I try to run it from my code.
Error: Cannot insert explicit value for identity column in table 'someTable' when IDENTITY_INSERT is set to OFF.
I have looked this up and there are a lot of suggestions on turning ON the IDENTITY_INSERT but they all use stored procedures. I need to be able to do this using a parametrized query. Or I need a way to get the newly created guid in the database. This seems like something everyone would need at some point. Are there any simple solutions?
PS. I need the ID because I need to add records to other tables using that ID.
EDIT:
Here is the Structure of the table in question
CREATE TABLE [dbo].[someTable]
(
[NewsID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_someTable_NewsID] DEFAULT (newid()),
[ShortTitle] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCreated] [datetime] NULL CONSTRAINT [DF_someTable_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_cmc_News_1] PRIMARY KEY CLUSTERED (
[NewsID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF
) ON [PRIMARY]
) ON [PRIMARY]
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
实际上，您一定有其他问题，或者表中存在其他列，因为如果表中有 IDENTITY 列，并且 IDENTITY 列不能是 uniqueidentifier 数据类型，您只会收到该错误 - 您确定吗正在插入到同一个表中，或者您没有使用某种试图为标识列指定特定值的 ORM 工具？也许您可以编写表定义并附加它？
编辑：添加附加信息，因为问题现在包含架构。
看起来这个特定的表中没有标识，所以我首先检查是否在相关表上定义了任何触发器，并查看是否在另一个使用标识的表中尝试插入...
Actually, you must have some other problem, or some other column in you table, because you only would get that error if you have an IDENTITY column in your table, and IDENTITY columns can't be of the datatype uniqueidentifier - are you sure you're inserting into the same table, or you aren't using an ORM tool of some kind that is trying to specify a specific value for an identity column? Perhaps you could script the table definition out and attach it?
EDIT: Adding additional info since the question now includes the schema.
Looks like there is no identity in this particular table, so I'd first check to see if you have any triggers defined on the table in question and see if there is an attempted insert taking place in another table that uses an identity...
首先，GUID 对于主键来说是一个糟糕的选择。
但是，如果您正在使用它们，则不应将相同的字段定义为身份字段。请发布您的表结构代码，我们可以帮助您正确地重新定义它。
IN the first place GUIDs area a horrible choice for primary keys.
If you are using them however, you should not also have the same filed defined as an identity field. Please post your table structure code and we can help you redefine it correctly.
问题是不允许您自己插入 ID（我认为这是因为引擎想要自己插入 ID）。要打开此功能，您需要在查询之前执行此查询：
The problem is that you are not allowed to insert an ID yourself (I assume this is because the engine wants to do it itself). To turn this feature on, you need to execute this query before your query:
您已将该字段的 Identity 属性设置为 On，这意味着数据库会在您插入记录时创建 ID。如果将该字段的“标识”设置为“关闭”，则可以在插入中设置该值。
或者，让数据库创建 id，然后获取创建的 id：
注意：存储过程和参数化查询并不互斥。我总是使用参数化查询来调用存储过程。
You have set the Identity property to On for the field, which means that the database creates the id when you insert a record. If you set Identity to Off for the field, you can set the value in the insert.
Alternatively, let the database create the id, then get the id that was created:
Note: A stored procedure and a parameterised query are not exclusive. I always call the stored procedures using a parameterised query.