SQL語法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的問題（上）

SQL Prompt是一款實用的SQL語法提示工具。它根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀——當開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據需要進行自定義，使之以預想的方式工作。

點擊下載SQL Prompt最新版

本文解釋了在向現有表添加非可空列或將包含空值的列更改為非可空時可能遇到的問題。演示了一個可以安全部署此類更改的遷移腳本。

在設計數據庫以添加或刪除NULL約束時，這是一種常見的儀式，但是當您對已經填充的表進行更改時，有一些問題可能會讓您感到痛苦。當您嘗試添加無法接受NULL值的新列或將現有的可空列更改為NOT NULL列時，可能會發(fā)生這種情況。如果SQL Prompt 檢測到將嘗試將NOT NULL列添加到現有表的代碼，則會向您發(fā)出警告（EI028），而不指定默認值。

我將演示這些問題，然后向您展示如何開發(fā)應用這些類型的更改構建腳本。我將展示這些如何工作，無論您是從頭開始構建新版本的表，還是更改，或者如果您需要更新現有表以便它包含這些更改。

將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版

在發(fā)布了這個表的第一個版本之后，我們很快意識到我們確實應該記錄該語言的名稱，因此我們改變了表的設計，以添加一個TheLanguage不能接受NULLs 的列。

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

SQL Prompt立刻警告我們危險：

如果我們忽略SQL Prompt的警告并執(zhí)行此操作，我們將會得到一個錯誤。

消息4901，級別16，狀態(tài)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，因此為了強制執(zhí)行一些數據完整性，我們需要創(chuàng)建一個名為“Location定義每種語言的父表” ，以及它們的最初記錄。

讓我們把它弄出來。對于這個表，我們不能只丟棄并重新創(chuàng)建沒有數據丟失的表，一旦我們的CountingWords表通過FOREIGN KEY約束引用該表，我們就會得到一個錯誤。我們需要采取預防措施。如果重新運行代碼，我將使用一種簡單但相當奇怪的技術，確保在數據丟失方面沒有損壞。此腳本必須作為多個批處理運行，因為CREATE TABLE語句必須位于批處理的開頭，并且很難跨批處理有條件地執(zhí)行代碼。

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將不允許我們使列不可為空，除非我們?yōu)樗峁┠J值，在這種情況下只是一個空字符串。

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

消息515，級別16，狀態(tài)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微信公眾號，及時獲取最新動態(tài)及最新資訊