SQL 当列已为 NOT NULL 时，无法在可空列上定义 PRIMARY KEY 约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22815683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot define PRIMARY KEY Constraint on nullable column when column is already NOT NULL
提问by Stephen Davlantes
I have a table called Casesthat has information about interviews such as IV_Date, IV_Length, Interviewer, etc. I also have a field within Casescalled Case_Code(a varchar) which is a six-character (three letters + three numbers) identifier; e.g. "ABC123" or "ZZZ999." There is a foreign key on ContactID(an int), which points to a Contacttable. I have created a computed column which is the PRIMARY KEY of this Casestable, called CaseID. CaseIDis simply a concatenation of Case_Codeand ContactID. So, ContactID"25" working on case "ZZZ999" is given a CaseIDof "ZZZ99925". Neither Case_Codenor ContactIDaccepts nulls, so CaseIDobviously does not. When setting up CaseIDI created it as NOT NULL, PRIMARY KEY, and used the formula "[Case_Code] + CONVERT([varchar], [contactID], 0)". I thought everything was working fine but when I try to rearrange any fields in the SQL Studio table design view, I get thrown this error:
我有一个表称为Cases具有约访谈如信息IV_Date，IV_Length，Interviewer等我也有内的字段Cases被称为Case_Code（A varchar），它是一个六字符（三个字母+三个数字）标识符; 例如“ABC123”或“ZZZ999”。ContactID(an int)上有一个外键，指向一个Contact表。我创建了一个计算列，它是这个Cases表的主键，称为CaseID. CaseID仅仅是的串联Case_Code和ContactID。因此，ContactID处理案例“ZZZ999”的“25”被赋予CaseID“ZZZ99925”。既不Case_Code也不ContactID接受空值，所以CaseID显然不接受。设置时CaseID我将其创建为 NOT NULL、PRIMARY KEY，并使用公式“[Case_Code] + CONVERT([varchar], [contactID], 0)”。我认为一切正常，但是当我尝试重新排列 SQL Studio 表设计视图中的任何字段时，我得到了这个错误：
'Cases (dbo)' table
- Unable to create index 'PK_Cases_1'.
Cannot define PRIMARY KEY constraint on nullable column in table 'Cases'.
Could not create constraint. See previous errors.'
I do not understand why I'm getting this error, since all NOT NULL columns in my table contain data. Any help or ideas would be greatly appreciated. Thanks!
我不明白为什么我会收到这个错误，因为我的表中的所有 NOT NULL 列都包含数据。任何帮助或想法将不胜感激。谢谢！
Edit with code:
用代码编辑：
CREATE TABLE [dbo].[Cases](
[ContactID] [int] NOT NULL,
[Case_Code] [varchar](16) NOT NULL,
[Assigned_To] [varchar](100) NULL,
[LEK_Interviewer] [varchar](255) NULL,
[Case_Notes] [varchar](max) NULL,
[IV_Status] [varchar](100) NULL,
[IV_Quality] [numeric](18, 0) NULL,
[IV_Date] [date] NULL,
[IV_Length] [varchar](50) NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](25) NULL,
[Country] [varchar](50) NULL,
[Total_Honorarium] [money] NULL,
[Currency] [varchar](20) NULL,
[Last_Update] [varchar](50) NULL,
[CaseID] AS ([Case_Code]+CONVERT([varchar],[contactID],0)) PERSISTED NOT NULL,
CONSTRAINT [PK_Cases_1] PRIMARY KEY CLUSTERED
(
[CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
回答by Omkar Hendre
A primary key cannot be applied on a column with a null constraint (nullable). When a table is created, by default every column is nullable.
主键不能应用于具有空约束（可为空）的列。创建表时，默认情况下每列都是可为空的。
So first off all we have to remove the null constraint and apply a NOT NULL constraint on the column. To do this execute the following query
因此，首先我们必须删除空约束并对列应用 NOT NULL 约束。为此，请执行以下查询
ALTER TABLE tbl_name alter column column_name int NOT NULL
After that you can apply a primary key on that same column. To do this execute the following query
之后，您可以在同一列上应用主键。为此，请执行以下查询
ALTER TABLE tbl_name ADD PRIMARY KEY (column_name)
I hope this will help you
我希望这能帮到您
回答by Stephen Davlantes
I bumped into this, too. You can create a PK on a calculated field, as long as it's marked NOT NULL, but the SQL Server table design interface does not handle this situation; it seems to mark all calculated fields as NULLable when modifying a table.
我也碰到了这个。可以在计算字段上创建PK，只要将其标记为NOT NULL即可，但是SQL Server表设计接口不处理这种情况；它似乎在修改表时将所有计算字段标记为 NULLable。
First, I strongly recommend that you always create and modify your tables with scripts, not the GUI. Doing so gives you a record of what you have and what changes you've made; you can run them to recreate your database as of any point in time. As HLGEM mentioned, you can also keep these scripts in source control. Having these scripts makes it easier to do things which are difficult to do with the GUI, and as you've discovered here, they make it possible to do things which are impossible in the GUI.
首先，我强烈建议您始终使用脚本而不是 GUI 创建和修改表。这样做可以让您记录您拥有的内容以及您所做的更改；您可以运行它们以在任何时间点重新创建您的数据库。正如 HLGEM 所提到的，您还可以将这些脚本保留在源代码管理中。使用这些脚本可以更轻松地完成在 GUI 中难以完成的事情，并且正如您在此处发现的那样，它们使在 GUI 中不可能完成的事情成为可能。
Within the table designer, delete your PK. You should then be able to save the table, effecting the reordering of fields you wanted to achieve. Next, you need to drop and recreate the calculated field, and finally you can recreate your PK. E.g.:
在表设计器中，删除您的 PK。然后您应该能够保存该表，从而实现您想要实现的字段的重新排序。接下来，您需要删除并重新创建计算字段，最后您可以重新创建您的 PK。例如：
ALTER TABLE Cases DROP COLUMN CaseID
ALTER TABLE Cases ADD CaseID AS (Case_Code + CAST(ContactID AS CHAR(6))) PERSISTED NOT NULL
ALTER TABLE Cases ADD CONSTRAINT CPK_Cases PRIMARY KEY CLUSTERED (CaseID)
If you have any indices which reference the calculated field (other than the PK, which we've already dropped), you'll need to delete them before you can drop the calculated field. Be sure to re-create them after, of course.
如果您有任何引用计算字段的索引（除了 PK，我们已经删除了），您需要先删除它们，然后才能删除计算字段。当然，一定要在之后重新创建它们。
Finally, while Dean did not support his assertion that calculated fields are bad for PKs, he is correct that it would be simpler to put your PK on { Case_Code, ContactID}. You could then stop persisting CaseID(if you even need it all) and save yourself a little disk space.
最后，虽然 Dean 不支持他的断言，即计算字段对 PK 不利，但他是正确的，将 PK 放在 { Case_Code, ContactID}上会更简单。然后你可以停止坚持CaseID（如果你甚至需要它）并为自己节省一点磁盘空间。
回答by amitkumar
Use this query
使用此查询
Alter table tablename
add column datatype identity(1,1)
回答by modelDBA
This appears to be a bug in the SQL Server Management Studio. The easiest approach is to make your changes using the designer. Then generate the change script using:
这似乎是 SQL Server Management Studio 中的错误。最简单的方法是使用设计器进行更改。然后使用以下命令生成更改脚本：
Right-click > Generate Change Script...
Copy the generated SQL or save it to a file. Then, add "NOT NULL" to the computed column in the CREATE TABLE statement and run the SQL file
复制生成的 SQL 或将其保存到文件中。然后，在 CREATE TABLE 语句的计算列中添加“NOT NULL”并运行 SQL 文件
回答by dean
Don't use computed column as a primary key, very bad idea. In your case, just define the PK on both CaseCode and ContactID columns. In the designer, select both columns, right-click and choose "set prinary key" from the menu.
不要使用计算列作为主键，非常糟糕的主意。在您的情况下，只需在 CaseCode 和 ContactID 列上定义 PK。在设计器中，选择两列，右键单击并从菜单中选择“设置主键”。
