使用ALTER TABLE修改表定義如增刪列與修改約束-雲原生資料庫 PolarDB-阿里雲

本文介紹了如何修改表。

簡介

當我們已經建立了一個表並意識到犯了一個錯誤或者應用需求發生改變時，我們可以移除表並重新建立它。但如果表中已經被填充資料或者被其他資料庫物件引用（例如有一個外鍵約束），這種做法就顯得很不方便。因此，本資料庫提供了一族命令來對已有的表進行修改。注意這和修改表中所包含的資料是不同的，這裡要做的是對錶的定義或者說結構進行修改。

利用這些命令，我們可以進行以下操作：

增加列
移除列
增加約束
移除約束
修改預設值
修改列資料類型
重新命名列
重新命名表

增加列

執行以下命令，增加一個列：

    ALTER TABLE products ADD COLUMN description text;

新列將被預設值所填充（如果沒有指定DEFAULT子句，則會填充空值）。

從本資料庫11開始，添加一個具有常量預設值的列不再意味著在執行ALTER TABLE 語句時需要更新表的每一行。相反，預設值將在下次訪問該行時返回，並在表被重寫時應用，從而使得ALTER TABLE即使在大表上也非常快。

但是，如果預設值是可變的（例如clock_timestamp()），則每一行需要被ALTER TABLE被執行時計算的值更新。為避免潛在的長時間的更新操作，特別是如果你想要用大多數非預設值填充列，那麼最好添加沒有預設值的列，再用 UPDATE插入正確的值，然後按照下面所述添加任何期望的預設值。

也可以同時為列定義約束，文法：

    ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

事實上CREATE TABLE中關於一列的描述都可以應用在這裡。記住不管怎樣，預設值必須滿足給定的約束，否則ADD將會失敗。也可以先將新列正確地填充好，然後再增加約束（見後文）。

移除列

執行以下命令，移除一個列：

    ALTER TABLE products DROP COLUMN description;

列中的資料將會消失。涉及到該列的資料表條件約束也會被移除。然而，如果該列被另一個表的外鍵所引用，本資料庫不會安靜地移除該約束。我們可以通過增加CASCADE來授權移除任何依賴於被刪除列的所有東西：

    ALTER TABLE products DROP COLUMN description CASCADE;

增加約束

為了增加一個約束，可以使用資料表條件約束的文法，例如：

    ALTER TABLE products ADD CHECK (name <> '');
    ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
    ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

要增加一個不能寫成資料表條件約束的非空約束，可使用文法：

    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

該約束會立即被檢查，所以表中的資料必須在約束被增加之前就已經符合約束。

移除約束

為了移除一個約束首先需要知道它的名稱。如果在建立時已經給它指定了名稱，那麼事情就變得很容易。否則約束的名稱是由系統產生的，我們必須先找出這個名稱。psql的命令\d ``表名將會對此有所協助，其他介面也會提供方法來查看錶的細節。因此命令如下：

    ALTER TABLE products DROP CONSTRAINT some_name;

說明

如果處理的是自動產生的約束名稱，如$2，別忘了用雙引號使它變成一個合法的標識符。

和移除一個列相似，如果需要移除一個被某些別的東西依賴的約束，也需要加上CASCADE。一個例子是一個外鍵約束依賴於被引用列上的一個唯一或者主鍵約束。

這對除了非空約束之外的所有約束類型都一樣有效。為了移除一個非空約束可以用：

    ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

更改列的預設值

執行以下命令，為一個列設定一個新預設值：

    ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

說明

這不會影響任何錶中已經存在的行，它只是為未來的INSERT命令改變了預設值。

執行以下命令，移除任何預設值：

    ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

說明

這等同於將預設值設定為空白值。相應的，試圖刪除一個未被定義的預設值並不會引發錯誤，因為預設值已經被隱式地設定為空白值。

修改列的資料類型

為了將一個列轉換為一種不同的資料類型，使用如下命令：

    ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

只有當列中的每一個項都能通過一個隱式造型轉換為新的類型時該操作才能成功。如果需要一種更複雜的轉換，應該加上一個USING子句來指定應該如何把舊值轉換為新值。

本資料庫將嘗試把列的預設值轉換為新類型，其他涉及到該列的任何約束也是一樣。但是這些轉換可能失敗或者產生奇特的結果。因此最好在修改類型之前先刪除該列上所有的約束，然後在修改完類型後重新加上相應修改過的約束。

重新命名列

執行以下命令，重新命名一個列：

    ALTER TABLE products RENAME COLUMN product_no TO product_number;

重新命名表

執行以下命令，重新命名一個表：

    ALTER TABLE products RENAME TO items;