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

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

點(diǎn)擊下載SQL Prompt最新版

本教程解釋了在向現(xiàn)有表添加非可空列或?qū)罩档牧懈臑榉强煽諘r(shí)可能遇到的問題。他演示了一個可以安全部署此類更改的遷移腳本。本文是教程的后半部分內(nèi)容，內(nèi)容緊接前文~

清單5：在創(chuàng)建列NOT NULL之前使用默認(rèn)值更新現(xiàn)有行

所以，效果很好。

推出所有變更

現(xiàn)在是時(shí)候?qū)⑺羞@些更改推廣到只有設(shè)計(jì)版本1、沒有Location表格、CountingWords沒有TheLanguage列和可空Word列的人員。

推出新Location表，因?yàn)樗窃摫淼牡谝粋€版本，不是真正的問題（參見清單3）。但是，將更改推廣到新CountingWords表需要添加一個TheLangauge不允許NULLs的Word列，并且NOT NULL在兩種情況下都要更改列，以避免我們已經(jīng)討論過的問題。

我們還希望我們的遷移腳本能夠正常工作，無論我們是否正在更新現(xiàn)有的v1 CountingWords，或者我們需要從頭開始構(gòu)建表的v2。此外，如果腳本意外重新運(yùn)行，我們不希望腳本造成任何傷害或觸發(fā)錯誤。

另外，在任何一種情況下，我們還需要將該TheLangauge列設(shè)置CountingWords為FOREIGN KEY自動更新以響應(yīng)父鍵的更新或刪除，以及更改PRIMARY KEY。作為最后一步，我們將添加11-20的舊威爾士語單詞，這是我們以前不知道的。

以下是遷移腳本，它將CountingWords從v1 遷移到v2，或者從頭開始創(chuàng)建v2，如果意外重新運(yùn)行，這將不會造成任何損害。在嘗試之前，要么刪除CountingWords表，要么重新運(yùn)行清單1以重新建立表的v1。

--we now script version 2
IF 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 nulls?
        (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;
GO
IF 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的安全遷移腳本

如果不是為了確保遷移腳本在所有情況下都能正常工作，那就簡單多了。我只是討厭只能在特定情況下運(yùn)行的構(gòu)建腳本。

現(xiàn)在讓我們通過在不同的語言或區(qū)域中添加一到二十的計(jì)數(shù)單詞來測試它：

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——在西坎布里亞郡數(shù)到20

現(xiàn)在我改變了主意。它不應(yīng)該被稱為“老威爾士”，而是“古老的威爾士”。真可惜，但我們現(xiàn)在可以測試我們的外鍵約束。

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

清單8：更改語言后的級聯(lián)更新

就像變魔法一樣，所有引用都已更改?，F(xiàn)在我有一個可以使用的數(shù)據(jù)庫！

我提供了一個FillCountingWordsTable  腳本，其中包含47個記錄位置和語言的完整計(jì)數(shù)單詞集，可用于更廣泛的測試。向美國的朋友和親戚道歉，我遺漏了幾個使用相同計(jì)數(shù)押韻的印第安部落。事實(shí)證明，他們被英國殖民者編織了，他們認(rèn)為用于計(jì)算縫線的詞語是魔法的一部分。

結(jié)論

我們剛剛建立了一個相當(dāng)精細(xì)的演示，說明如何避免更改已填充數(shù)據(jù)的表的一些問題。在此示例中，問題涉及NULL值的使用，當(dāng)您嘗試添加無法接受NULL值的新列時(shí)，或者NOT NULL當(dāng)列中存在現(xiàn)有NULL值時(shí)，將現(xiàn)有的可空列更改為列時(shí)，會發(fā)生這些問題。在第一種情況下，您只需DEFAULT首先向列添加約束，使用值不是NULL，在第二種情況下，NULL首先通過更新表來刪除值。

這些是對現(xiàn)有表進(jìn)行更改的技術(shù)，這就是為什么我詳細(xì)闡述了演示如何將這兩個操作作為彈性腳本的一部分進(jìn)行操作的原因，無論是新建還是遷移，都可以運(yùn)行，這可以重新運(yùn)行而不會產(chǎn)生任何不利影響。

相關(guān)文章：

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

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊“咨詢在線客服”

掃描關(guān)注慧聚IT微信公眾號，及時(shí)獲取最新動態(tài)及最新資訊