-- 新增列
ALTER TABLE distributors ADD COLUMN address varchar(30);
-- 刪除列
ALTER TABLE distributors DROP COLUMN address RESTRICT;
-- 修改列類型(單列)
ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80);
-- 批量修改多列
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);
-- 列重命名
ALTER TABLE distributors RENAME COLUMN address TO city;
-- 使用 USING 子句轉換數據(如 Unix 時間戳 → 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 TO suppliers;
-- 移動表空間
ALTER TABLE distributors SET TABLESPACE fasttablespace;
-- 非空約束
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 tableName ADD columnName varchar(30);
-- 修改列類型
ALTER TABLE tableName ALTER COLUMN columnName varchar(4000);
-- 重命名列 (系統預存過程)
EXEC sp_rename 'tableName.column1', 'column2', 'COLUMN';
-- 刪除列
ALTER TABLE tableName DROP COLUMN columnName;
-- 重命名表
EXEC sp_rename 'tableName', 'newTableName';
DROP COLUMN 不會立即釋放空間,需 DBCC CLEANTABLE 或重建叢集索引。PK_表、FK_表_引用表、UQ_表_列、CK_表_業務含義。熟練掌握 ALTER TABLE 各子句,能讓結構變更更安全、可控,是 DBA 與開發必備基本功。