SQL语法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的问题（上）

SQL Prompt是一款实用的SQL语法提示工具。它根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读——当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt最新版

本文解释了在向现有表添加非可空列或将包含空值的列更改为非可空时可能遇到的问题。演示了一个可以安全部署此类更改的迁移脚本。

在设计数据库以添加或删除NULL约束时，这是一种常见的仪式，但是当您对已经填充的表进行更改时，有一些问题可能会让您感到痛苦。当您尝试添加无法接受NULL值的新列或将现有的可空列更改为NOT NULL列时，可能会发生这种情况。如果SQL Prompt 检测到将尝试将NOT NULL列添加到现有表的代码，则会向您发出警告（EI028），而不指定默认值。

我将演示这些问题，然后向您展示如何开发应用这些类型的更改构建脚本。我将展示这些如何工作，无论您是从头开始构建新版本的表，还是更改，或者如果您需要更新现有表以便它包含这些更改。

将NOT NULL列添加到已填充的表中

我们有一张表，CountingWords在那里我们记录旧威尔士人用来计算的字数。我们的目标是计算到20，但目前只知道如何数到10。

/* we create a table. Just for our example, we create the words
used to count from one to ten in old Welsh.
(Na, nid wyf yn siaradwr Cymraeg ond rwy'n hoffi gwneud 
rhywfaint o ymchwil ieithyddol gyda'r nos)
I'd like to do from eleven to twenty as well eventually
so I'll add them and leave them NULL.
Here is the initial build script, with guard clauses
of course. We'll re-create every time it is run. 
With a few modifications we can make it so it only runs once 
which is safer if you have reckless colleagues in your shop.
 */
IF Object_Id('dbo.CountingWords') IS NOT NULL DROP TABLE dbo.CountingWords;
--we script version 1 of our table of counting words
CREATE TABLE dbo.CountingWords
  (
  TheValue INT NOT NULL,
  Word NVARCHAR(30) NULL,
  CONSTRAINT CountingWordsPK PRIMARY KEY (TheValue)
  );
GO
INSERT INTO dbo.CountingWords (TheValue, Word)
VALUES
  (1, 'Un'),  (2, 'Dau'),  (3, 'Tri'),  (4, 'Pedwar'),
  (5, 'Pump'),  (6, 'Chwech'),  (7, 'Saith'),  (8, 'Wyth'),
  (9, 'Naw'),  (10, 'Deg'),  (11, NULL),  (12, NULL),
  (13, NULL),  (14, NULL),  (15, NULL),  (16, NULL),
  (17, NULL),  (18, NULL),  (19, NULL),  (20, NULL);
GO

清单1：Un，Dau，Tri - CountingWords表的第1版

在发布了这个表的第一个版本之后，我们很快意识到我们确实应该记录该语言的名称，因此我们改变了表的设计，以添加一个TheLanguage不能接受NULLs 的列。

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL;

SQL Prompt立刻警告我们危险：

如果我们忽略SQL Prompt的警告并执行此操作，我们将会得到一个错误。

消息4901，级别16，状态1，行34 

ALTER TABLE仅允许添加可以包含空值的列，或者指定了DEFAULT定义，或者添加的列是标识或时间戳列，或者如果没有以前的列条件满足表必须为空以允许添加此列。列'TheLanguage'不能添加到非空表'CountingWords'，因为它不满足这些条件。

错误消息是显式的，可以通过定义DEFAULT约束来轻松修复，以便SQL Server可以为每一行插入此新列的默认值。

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL
          DEFAULT 'Old Welsh';

清单2：添加NOT NULL列时指定默认值

简而言之，如果要添加列，并且不允许NULL值，则必须提供一个值以放入每一行。因为我们的表现在只有一种语言，“老威尔士语”，这不是太难。

当然，我们也想记录如何计算其他语言，例如Manx、Cornish或Cumbrian，因此为了强制执行一些数据完整性，我们需要创建一个名为“Location定义每种语言的父表” ，以及它们的最初记录。

让我们把它弄出来。对于这个表，我们不能只丢弃并重新创建没有数据丢失的表，一旦我们的CountingWords表通过FOREIGN KEY约束引用该表，我们就会得到一个错误。我们需要采取预防措施。如果重新运行代码，我将使用一种简单但相当奇怪的技术，确保在数据丢失方面没有损坏。此脚本必须作为多个批处理运行，因为CREATE TABLE语句必须位于批处理的开头，并且很难跨批处理有条件地执行代码。

IF Object_Id('dbo.Location') IS NOT NULL SET NOEXEC ON;
 --cunning way of only executing a section
--of code on a condition. until the next SET NOEXEC OFF
--we script version 1 of our table of counting words
GO
--sadly the create table statement has to be at the start of a batch
CREATE TABLE dbo.Location
  (
  TheLanguage NVARCHAR(30) NOT NULL,
  Description VARCHAR(100) NOT NULL DEFAULT '',
  CONSTRAINT LanguageKey PRIMARY KEY (TheLanguage)
  );
--now we insert the row we need for our existing data
INSERT INTO dbo.Location (TheLanguage) VALUES ('Old Welsh');
GO
SET NOEXEC OFF;

清单3：Location表的版本1

当然，现在我们还需要修改CountingWords表，使其TheLanguage列为a FOREIGN KEY，引用新Location表，但我们稍后会处理它。

更改可空列以使其不可为空

很快，我们决定允许列中的NULL值是我们想要解决的设计错误。我们已经了解到，如果表包含数据，SQL Server将不允许我们使列不可为空，除非我们为它提供默认值，在这种情况下只是一个空字符串。

ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT '' FOR Word;
ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

消息515，级别16，状态2，行58 

不能将值NULL插入“Word”列，表'PhilFactor.dbo.CountingWords'; 列不允许空值。更新失败。

该语句已终止。

清单4：尝试使Word列NOT NULL失败

AIEE！我们仍然无法使列不可为空，即使我们告诉SQL Server要为NULL列插入什么！首先，我们必须通过使用默认值更新所有行来显式删除任何现有的NULL值：

UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;
ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

本教程内容尚未结束，彩票走势图相关的文章：

SQL语法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的问题（下）

想要购买SQL Prompt正版授权，或了解更多产品信息请点击

扫描关注慧聚IT微信公众号，及时获取最新动态及最新资讯