SQL Server:列具有不同数据类型时的复合 PK 问题 [英] SQL Server: composite PK issue when columns having different data type

问题描述

看到我的代码它不起作用但不明白为什么?

see my code it is not working but could not understand why?

我试图创建具有不同数据类型的列的复合 pk

i was trying to create composite pk having columns with different data type

当我试过这个

CREATE TABLE [dbo].[ControllerActionItems](
    [ControllerName] [varchar](50) NULL,
    [ActionName] [varchar](50) NULL,
    [RoleID] [int] NULL,

    primary key ([ControllerName], [ActionName],[RoleID])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

收到此错误消息

Msg 1709, Level 16, State 1, Line 2 不能使用 TEXTIMAGE_ON表没有文本，ntext，图像，varchar(max)，nvarchar(max)，非 FILESTREAM varbinary(max)、xml 或大型 CLR 类型列.

Msg 1709, Level 16, State 1, Line 2 Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), non-FILESTREAM varbinary(max), xml or large CLR type columns.

解决了更新的代码

CREATE TABLE [dbo].[ControllerActionItems](
    [ControllerName] [varchar](50) NOT NULL,
    [ActionName] [varchar](50) NOT NULL,
    [RoleID] [int] NOT NULL,

    primary key ([ControllerName], [ActionName],[RoleID])
) 

推荐答案

如错误所述，您需要指定的列之一使用 TEXTIMAGE_ON，例如如果您使用了 varchar(MAX)varchar(50).但是，根据 this other answer 无论如何，您所做的似乎都是多余的，因为默认行为是在 PRIMARY 中存储大文本值列.

As the error states, you need one of the indicated columns to use TEXTIMAGE_ON, such as if you used varchar(MAX) instead of varchar(50). However, according to this other answer what you're doing seems redundant anyways, as the default behavior is to store large-text-value columns in PRIMARY.

除非表中确实有大格式列，否则您应该简单地从 SQL 语句中删除 TEXTIMAGE_ON [PRIMARY].

Unless you actually have a large-format column in the table, you should simply remove TEXTIMAGE_ON [PRIMARY] from the SQL statement.

这篇关于SQL Server:列具有不同数据类型时的复合 PK 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！