Alter table

Alter table，網路程式及編程中所用的術語。通過更改、添加、除去列和約束，或者通過啟用或禁用約束和觸發器來更改表的定義。

基本介紹

• 外文名：Alter table
• 子形式：ADD COLUMN等
• 含義：網路程式及編程中所用的術語
• 定義：通過更改、添加、除去列來更改表

資料庫語言

編輯本義項

求助編輯

Alter

資料庫SQL語言的修改語句，可以用來修改基本表，其一般表示格式為：

ALTER TABLE<表名>[改變方式]

改變方式：

· 加一個欄位: ADD "欄位 1" "欄位 1 資料種類"

· 刪去一個欄位: DROP "欄位 1"

· 改變欄位名稱: CHANGE "原本欄位名" "新欄位名" "新欄位名資料種類"

· 改變欄位的資料種類: MODIFY "欄位 1" "新資料種類"

由上可以看出，修改基本表提供如下四種修改方式：

（1）ADD方式：用於增加新列和完整性約束，列的定義方式同CREARE TABLE語句中的列定義方式相同，其語法格式：

ALTER TABLE <表名> ADD <列定義>|<完整性約束>。由於使用此方式中增加的新列自動填充NULL值，所以不能為增加的新列指定NOT NULL約束。

（2）DROP方式：用於刪除指定的完整性約束條件，或刪指定的列，其語法格式為：

ALTER TABLE<表名> DROP [<完整性約束名>]

ALTER TABLE<表名> DROP COLUMN <列名>

注釋：某些資料庫系統不允許這種在資料庫表中刪除列的方式 (DROP COLUMN <列名>)。

(3)CHANGE方式，用於修改某些列，其語法格式：

ALTER TABLE [表名] CHANGE <原列名> TO <新列名><新列的數據類型>

(4)MODIFY方式，用於修改某些列的數據類型，其語法格式：

ALTER TABLE [表名] MODIFY [列名] [數據類型]

子形式簡介

它有好幾種子形式：

ADD COLUMN

這種形式用和 CREATE TABLE 里一樣的語法向表中增加一個新的欄位。

DROP COLUMN

這種形式從表中刪除一個欄位。請注意，和這個欄位相關的索引和表約束也會被自動刪除。 如果任何表之外的對象依賴於這個欄位， 你必須說 CASCADE，比如，外鍵參考，視圖等等。

ALTER COLUMN TYPE

這種類型改變表中一個欄位的類型。該欄位涉及的索引和簡單的表約束將被自動地轉換為使用新的欄位類型， 方法是重新分析最初提供的表達式。可選的 USING 子句聲明如何從舊的欄位值里計算新的欄位值； 如果省略，那么預設的轉換就是從舊類型向新類型的賦值轉換。如果從舊數據類型到新類型沒有隱含或者賦值的轉換， 那么必須提供一個 USING。

SET/DROP DEFAULT

這種形式為一個欄位設定或者刪除預設值。請注意預設值只套用於隨後的 INSERT 命令； 它們不會導致已經在表中的行的數值的修改。我們也可以為視圖創建預設， 這個時候它們是在視圖的 ON INSERT 規則套用之前插入 INSERT 語句中去的。

SET/DROP NOT NULL

這些形式修改一個欄位是否標記為允許 NULL 值或者是拒絕 NULL 值。 如果表在欄位中包含非空值，那么你只可以 SET NOT NULL。

SET STATISTICS

這個形式為隨後的 ANALYZE 操作設定每欄位的統計收集目標（default_statistics_target）。 目標的範圍可以在 0 到 1000 之內設定；另外，把他設定為 -1 則表示重新恢復到使用系統預設的統計目標。 有關 PostgreSQL 查詢規劃器使用的統計信息的更多信息。

SET STORAGE

這種形式為一個欄位設定存儲模式。這個設定控制這個欄位是內聯保存還是保存在一個附屬的表里，以及數據是否要壓縮。 PLAIN 必需用於定長的數值，比如 integer，並且是內聯的，不壓縮的。 MAIN 用於內聯，可壓縮的數據。 EXTERNAL 用於外部保存，不壓縮的數據， 而 EXTENDED 用於外部的壓縮數據。 EXTENDED 是大多數支持非 PLAIN 存儲的數據之預設。 使用 EXTERNAL 將令在 text 欄位上的子字串操作更快， 付出的代價是增加了存儲空間。 請注意 SET STORAGE 本身並不改變表上的任何東西， 只是設定將來的表操作時，建議使用的策略。

ADD TABLE_CONSTRAINT

這個形式給表增加一個新的約束，用的語法和 CREATE TABLE 一樣。

DROP CONSTRAINT

這個形式刪除一個表上的約束。 目前，在表上的約束不要求有唯一的名字，因此可能有多個約束匹配聲明的名字。 所有這樣的約束都將被刪除。

CLUSTER

這種形式為將來的 CLUSTER 選項選擇預設索引。它實際上並不重新對表建簇。

SET WITHOUT CLUSTER

這種形式從表中刪除最新使用的 CLUSTER 索引。 這樣會影響將來那些沒有聲明索引的建簇操作。

SET WITHOUT OIDS

這種形式從表中刪除 oid 系統欄位。 它和 DROP COLUMN oid RESTRICT 完全相同， 只不過是如果表上已經沒有 oid 欄位了，那么它不會報告錯誤。

請注意，不存在某種 ALTER TABLE 的變種可以在刪除了 OID 之後再把它們恢復回來。

OWNER

這個形式改變表，索引，序列或者視圖的所有者為指定所有者。

SET TABLESPACE

這種形式把表的表空間修改為指定的表空間並且把與表相關的數據檔案移動到新的表空間去。 如果在表上呦索引，則不會移動。但是他們可以通過額外的 SET TABLESPACE 命令移動。參閱 CREATE TABLESPACE。

RENAME

RENAME 形式改變一個表（或者一個索引，序列，或者視圖）的名字， 或者是表中獨立欄位的名字。它們對存儲的數據沒有影響。

除了 RENAME 之外所有動作都可以捆綁再一個多次修改的列表中同時施用。 比如，我們可以在一個命令里增加幾個欄位和/或修改幾個欄位的類型。 對於大表，這么做特別有用，因為只需要對該表做一次處理。

要使用 ALTER TABLE，你必需擁有該表； 除了 ALTER TABLE OWNER 之外，它只能由超級用戶執行。

參數

table

試圖更改的現存表（可能有模式修飾）的名稱。 如果聲明了 ONLY，則只更改該表。 如果沒有聲明 ONLY，則該表及其所有後代表（如果有）都被更新。 我們可以在表名字後面附加一個 * 表示後代表都被掃描，但是在目前的版本里，這是預設行為。 （在7.1之前的版本，ONLY 是預設的行為。）預設可以通過改變配置選項 sql_inheritance 來改變。

column

現存或新的欄位名稱。

new_column

現存欄位的新名稱。

new_name

表的新名稱。

type

新欄位的類型，或者現存欄位的新類型。

table_constraint

表的新的約束定義。

constraint_name

要刪除的現有約束的名字。

index_name

要標記為建簇的表上面的索引名字。

CASCADE

自動刪除依賴於被依賴欄位或者約束的對象（比如，引用該欄位的視圖）。

RESTRICT

如果欄位或者約束還有任何依賴的對象，則拒絕刪除該欄位。 這是預設行為。

new_owner

該表的新所有者的用戶名。

tablespace_name

這個表將要移動往的表空間名字。

注意事項

COLUMN 關鍵字是多餘的，可以省略。

如果用 ADD COLUMN 增加一個欄位，那么所有表中現有行都初始化為該欄位的預設值 （如果沒有聲明 DEFAULT 子句，那么就是 NULL）。

用一個非空預設增加一個欄位或者是改變一個欄位的現有類型會要求整個表的重寫。 對於大表來說，這個操作可能會花很長時間；並且它還臨時需要兩倍的磁碟空間。

增加一個 CHECK 或者 NOT NULL 約束要求掃描該表以保證現有的行複合約束要求。

提供在一個 ALTER TABLE 裡面聲明多個修改的主要原因是原先需要的對表的多次掃描和重寫可以組合成一個回合。

DROP COLUMN 命令並不是物理上把欄位刪除， 而只是簡單地把它標記為 SQL 操作中不可見的。隨後對該表的插入和更新將在該欄位存儲一個 NULL。 因此，刪除一個欄位是很快的，但是它不會立即縮減你的表在磁碟上的大小，因為被刪除了的欄位占據的空間還沒有回收。 這些空間將隨著現有的行的更新而得到回收。

ALTER TYPE 要求重寫整個表的特性有時候是一個優點， 因為重寫的過程消除了任何表中的沒用的空間。比如，要想立刻回收被一個已經刪除的欄位占據的空間， 最快的方法是：

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

這裡 anycol 是任何在表中還存在的欄位，而 anytype 是和該欄位的原類型一樣的類型。 這樣的結果是在表上沒有任何可見的語意的變化，但是這個命令強迫重寫，這樣就刪除了不再使用的數據。

ALTER TYPE 的 USING 選項實際上可以聲明涉及該行舊值的任何表達式； 也就是說，它可以引用除了正在被轉換的欄位之外其它的欄位。這樣，我們就可以用 ALTER TYPE 語法做非常普遍性的轉換。因為這個靈活性，USING 表達式並沒有施用於該欄位的預設值（如果有的話）； 結果可能不是預設表達式要求的常量表達式。 這就意味著如果從舊類型到新類型如果沒有隱含或者賦值轉換的話， 那么即使存在 USING 子句的情況下， ALTER TYPE 也可能無法把預設值轉換成新的類型。 在這種情況下，我們應該用 DROP DEFAULT 先刪除預設， 執行 ALTER TYPE，然後使用 SET DEFAULT 增加一個合適的新預設。 類似的考慮也適用於涉及該欄位的索引和約束。

如果表有任何後代表，那么如果不在後代表上做同樣的修改的話， 就不允許在父表上增加，重命名或者修改一個欄位的類型，也就是說， ALTER TABLE ONLY將被拒絕。這樣就保證了後代表總是有和父表匹配的欄位。

一個遞歸DROP COLUMN 操作將只有在後代表並不從任何其它父表中繼承該欄位並且從來沒有獨立定義該欄位的時候才能刪除一個後代表的欄位。 一個非遞歸的DROP COLUMN（也就是，ALTER TABLE ONLY ... DROP COLUMN）從來不會刪除任何後代欄位， 而是把他們標記為獨立定義的，而不是繼承的。

不允許更改系統表結構的任何部分。

舉例

向表中增加一個 varchar 列：

ALTER TABLE distributors ADD address varchar(30);

從表中刪除一個欄位：

ALTER TABLE distributors DROP COLUMN address RESTRICT;

在一個操作中修改兩個現有欄位的類型：

ALTER TABLE distributors

ALTER COLUMN address TYPE varchar(80),

ALTER COLUMN name TYPE varchar(100);

使用一個 USING 子句， 把一個包含 UNIX 時間戳的 integer 欄位轉化成 timestamp with time zone：

ALTER TABLE foo

ALTER COLUMN foo_timestamp TYPE timestamp with time zone

USING

timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

對現存欄位改名：

ALTER TABLE distributors RENAME COLUMN address TO city;

更改現存表的名字：

ALTER TABLE distributors RENAME TO suppliers;

給一個欄位增加一個非空約束：

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

從一個欄位里刪除一個非空約束：

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

給一個表增加一個檢查約束：

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

刪除一個表和它的所有子表的檢查約束：

ALTER TABLE distributors DROP CONSTRAINT zipchk;

向表中增加一個外鍵約束：

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;

給表增加一個（多欄位）唯一約束：

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

給一個表增加一個自動命名的主鍵約束，要注意的是一個表只能有一個主鍵：

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

把表移動到另外一個表空間：

ALTER TABLE distributors SET TABLESPACE fasttablespace;

兼容性

ADD，DROP，和 SET DEFAULT 形式與 SQL 標準兼容。 其它形式是 PostgreSQL 對 SQL 標準的擴展。 還有，在一個 ALTER TABLE 命令里聲明多於一個操作也是擴展。

ALTER TABLE DROP COLUMN 可以用於刪除表中的唯一的一個欄位， 留下一個零欄位的表。這是對 SQL 的擴展，它不允許零欄位表。

使用區別

db2與oracle、mysql使用區別

1,增加列:相同
alter table test add mail varchar(128);

2,刪除列:
oracle 與mysql相同:alter table test drop column mail;
db2 ：alter table test drop column mail 刪除列後需要reorg table（重組表）

3,更改列名
oracle : alter table test rename column mail to mail2;
mysql : alter talbe test change mail mail2 varchar(128);
db2 : 不提供更改列名功能(解決辦法同刪除，或者通過建立一個新視圖解決)

4,更改列類型
oracle :alter table test modify column (mail2 integer);
mysql :alter table test modify column mail2 integer;
db2 :alter table test alter mail varchar(256) 只可以加寬，不能更改類型

5,更改列的限制(主鍵、非空)
db2 :alter table test alter mail null/not null;
mysql :alter table test modify mail2 varchar(29) not null;
oracle:alter table test modify mail2 null/not null;