Alter table

編輯本義項

求助編輯

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 [列名] [數據類型]

它有好幾種子形式：

這種形式用和 CREATE TABLE 裏一樣的語法向表中增加一個新的字段。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

現存或新的字段名稱。

現存字段的新名稱。

表的新名稱。

新字段的類型，或者現存字段的新類型。

表的新的約束定義。

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

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

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

如果字段或者約束還有任何依賴的對象，則拒絕刪除該字段。 這是缺省行為。

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

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

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;