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

本教程解释了在向现有表添加非可空列或将包含空值的列更改为非可空时可能遇到的问题。他演示了一个可以安全部署此类更改的迁移脚本。本文是教程的后半部分内容，内容紧接前文~

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

SQL Prompt最新版

本教程解释了在向现有表添加非可空列或将包含空值的列更改为非可空时可能遇到的问题。他演示了一个可以安全部署此类更改的迁移脚本。本文是教程的后半部分内容，内容紧接前文~

清单5：在创建列NOT NULL之前使用默认值更新现有行

所以，效果很好。

推出所有变更

现在是时候将所有这些更改推广到只有设计版本1、没有Location表格、CountingWords没有TheLanguage列和可空Word列的人员。

推出新Location表，因为它是该表的第一个版本，不是真正的问题（参见清单3）。但是，将更改推广到新CountingWords表需要添加一个TheLangauge不允许NULLs的Word列，并且NOT NULL在两种情况下都要更改列，以避免我们已经讨论过的问题。

我们还希望我们的迁移脚本能够正常工作，无论我们是否正在更新现有的v1 CountingWords，或者我们需要从头开始构建表的v2。此外，如果脚本意外重新运行，我们不希望脚本造成任何伤害或触发错误。

另外，在任何一种情况下，我们还需要将该TheLangauge列设置CountingWords为FOREIGN KEY自动更新以响应父键的更新或删除，以及更改PRIMARY KEY。作为最后一步，我们将添加11-20的旧威尔士语单词，这是我们以前不知道的。

以下是迁移脚本，它将CountingWords从v1 迁移到v2，或者从头开始创建v2，如果意外重新运行，这将不会造成任何损害。在尝试之前，要么删除CountingWords表，要么重新运行清单1以重新建立表的v1。

--we now script version 2IF Object_Id('dbo.CountingWords') IS NULL  BEGIN    --we script version 2 of our table of counting words if it    --doesn't already exist    CREATE TABLE dbo.CountingWords      (      TheValue INT NOT NULL,      Word NVARCHAR(30) NOT NULL CONSTRAINT WordConstraint DEFAULT '',      TheLanguage NVARCHAR(30) NOT NULL         CONSTRAINT LanguageConstraint REFERENCES dbo.Location(TheLanguage)          ON DELETE CASCADE          ON UPDATE CASCADE        CONSTRAINT CountingWordsPK PRIMARY KEY(TheValue, TheLanguage)      );  END;ELSE /* else we need to add a column and change the primary key     constraint */  BEGIN    IF NOT EXISTS -- only run if the column does not exist      (      SELECT *  FROM sys.columns        WHERE name LIKE 'TheLanguage'          AND object_id = Object_Id('dbo.CountingWords')      )      BEGIN         -- first we need to add the language column        ALTER TABLE CountingWords ADD TheLanguage NVARCHAR(30) NOT NULL          DEFAULT 'Old Welsh' CONSTRAINT LanguageConstraint             REFERENCES dbo.Location(TheLanguage)             ON DELETE CASCADE             ON UPDATE CASCADE ;      END       --now we need to alter the primary key      ALTER TABLE CountingWords DROP CONSTRAINT CountingWordsPK;      ALTER TABLE CountingWords --and add the new version        ADD CONSTRAINT CountingWordsPK           PRIMARY KEY(TheValue, TheLanguage);      IF NOT EXISTS -- do we need to add the default and remove the nullsnbsp;       (SELECT * FROM sys.default_constraints           WHERE name LIKE 'WordConstraint')        BEGIN          ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT '' FOR Word;/* You can specify NOT NULL in ALTER COLUMN only if the column contains no null values. The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed,*/          UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;        END;    IF NOT EXISTS --now finally we can make it not null         (SELECT * FROM sys.columns            WHERE name LIKE 'word' AND is_nullable = 0)           ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;  END;GOIF EXISTS --do we need to add in the welsh words we didn't know   (SELECT * FROM dbo.CountingWords  WHERE TheLanguage LIKE 'Old Welsh' AND word LIKE ''  )  --yes we need to add those words to replace those pesky blanks  UPDATE CountingWords    SET Word = welsh.word    FROM CountingWords AS cw      INNER JOIN        (        VALUES ('Un ar ddeg', 11), ('Deuddeg', 12), ('Tri ar ddeg', 13),          ('Pedwar ar ddeg', 14), ('Pymtheg', 15), ('Un ar bymtheg', 16),          ('Dau ar bymtheg', 17), ('Deunaw', 18), ('Pedwar ar bymtheg', 19),          ('Ugain', 20)        ) AS welsh (word, meaning)       ON welsh.meaning = cw.TheValue;

清单6：CountingWords v2的安全迁移脚本

如果不是为了确保迁移脚本在所有情况下都能正常工作，那就简单多了。我只是讨厌只能在特定情况下运行的构建脚本。

现在让我们通过在不同的语言或区域中添加一到二十的计数单词来测试它：

DECLARE @Language NVARCHAR(30) = 'West Cumbrian';IF NOT EXISTS   (SELECT * FROM dbo.Location   WHERE TheLanguage LIKE @Language  ) INSERT INTO dbo.Location (TheLanguage)       VALUES (@Language);/* and now we can add in any other ways of counting up to twenty that we want */IF NOT EXISTS (SELECT * FROM CountingWords WHERE Thelanguage LIKE @Language)INSERT INTO CountingWords (TheValue, Word, TheLanguage)  SELECT TheValue, word, @Language    FROM      (      VALUES ('yan', 1), ('tyan', 2), ('tethera', 3), ('methera', 4),        ('pimp', 5), ('sethera', 6), ('lethera', 7), ('hovera', 8),        ('dovera', 9), ('dick', 10), ('yan-a-dick', 11), ('tyan-a-dick', 12),        ('tethera-dick', 13), ('nethera-dick', 14), ('bumfit', 15),        ('yan-a-bumfit', 16), ('tyan-a-bumfi t', 17), ('tithera-bumfit', 18),        ('methera-bumfit', 19), ('giggot', 20)      ) AS f (word, TheValue);GO

清单7：Yan，Tyan，Tethera——在西坎布里亚郡数到20

现在我改变了主意。它不应该被称为“老威尔士”，而是“古老的威尔士”。真可惜，但我们现在可以测试我们的外键约束。

UPDATE dbo.Location SET TheLanguage = 'Archaic Welsh' WHERE TheLanguage LIKE 'Old Welsh' SELECT * FROM locationSELECT * FROM CountingWords

清单8：更改语言后的级联更新

就像变魔法一样，所有引用都已更改。现在我有一个可以使用的数据库！

我提供了一个FillCountingWordsTable  脚本，其中包含47个记录位置和语言的完整计数单词集，可用于更广泛的测试。向美国的朋友和亲戚道歉，我遗漏了几个使用相同计数押韵的印第安部落。事实证明，他们被英国殖民者编织了，他们认为用于计算缝线的词语是魔法的一部分。

结论

我们刚刚建立了一个相当精细的演示，说明如何避免更改已填充数据的表的一些问题。在此示例中，问题涉及NULL值的使用，当您尝试添加无法接受NULL值的新列时，或者NOT NULL当列中存在现有NULL值时，将现有的可空列更改为列时，会发生这些问题。在第一种情况下，您只需DEFAULT首先向列添加约束，使用值不是NULL，在第二种情况下，NULL首先通过更新表来删除值。

这些是对现有表进行更改的技术，这就是为什么我详细阐述了演示如何将这两个操作作为弹性脚本的一部分进行操作的原因，无论是新建还是迁移，都可以运行，这可以重新运行而不会产生任何不利影响。

相关文章：

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

想要购买SQL Prompt正版授权，或了解更多产品信息请点击“咨询在线客服”

标签：

来源：慧都