ALTER TABLE 敘述句 (SQL ALTER TABLE Statement)

ALTER TABLE 敘述句用於修改已存在的資料表結構，包括新增、修改、刪除欄位，以及管理資料表的約束條件 (Constraints)。

基本語法：

ALTER TABLE table_name 操作指令;

假設我們有一個 customers 資料表：

新增欄位 (ADD COLUMN)

使用 ADD 來新增欄位到資料表中。

語法：

ALTER TABLE table_name
ADD column_name datatype;

範例： 新增一個 Email 欄位

ALTER TABLE customers
ADD Email VARCHAR(100);

一次新增多個欄位：

ALTER TABLE customers
ADD Email VARCHAR(100),
ADD Birthday DATE,
ADD Discount DECIMAL(5, 2);

💡 注意： 新增的欄位預設值為 NULL，除非你指定 DEFAULT 值或 NOT NULL 約束。

新增欄位並設定預設值：

ALTER TABLE customers
ADD Status VARCHAR(20) DEFAULT 'active';

修改欄位 (MODIFY / ALTER COLUMN)

修改欄位的資料型別或屬性。

⚠️ 不同資料庫語法不同：

MySQL：

ALTER TABLE table_name
MODIFY column_name new_datatype;

SQL Server：

ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

PostgreSQL：

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;

範例： 將 Phone 欄位從 VARCHAR(20) 改為 VARCHAR(50)

MySQL：

ALTER TABLE customers
MODIFY Phone VARCHAR(50);

SQL Server：

ALTER TABLE customers
ALTER COLUMN Phone VARCHAR(50);

PostgreSQL：

ALTER TABLE customers
ALTER COLUMN Phone TYPE VARCHAR(50);

刪除欄位 (DROP COLUMN)

使用 DROP COLUMN 來刪除資料表中的欄位。

語法：

ALTER TABLE table_name
DROP COLUMN column_name;

範例： 刪除 Address 欄位

ALTER TABLE customers
DROP COLUMN Address;

⚠️ 注意： 刪除欄位會連同該欄位中的所有資料一併刪除，此操作無法復原！

重新命名欄位 (RENAME COLUMN)

MySQL：

ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype;

SQL Server / PostgreSQL：

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

範例： 將 Phone 欄位重新命名為 Mobile

MySQL：

ALTER TABLE customers
CHANGE Phone Mobile VARCHAR(20);

PostgreSQL / SQL Server：

ALTER TABLE customers
RENAME COLUMN Phone TO Mobile;

重新命名資料表 (RENAME TABLE)

MySQL：

ALTER TABLE old_table_name
RENAME TO new_table_name;

或

RENAME TABLE old_table_name TO new_table_name;

SQL Server：

EXEC sp_rename 'old_table_name', 'new_table_name';

PostgreSQL：

ALTER TABLE old_table_name
RENAME TO new_table_name;

新增約束條件 (ADD CONSTRAINT)

新增主鍵 (PRIMARY KEY)

ALTER TABLE customers
ADD PRIMARY KEY (C_Id);

或使用命名約束：

ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (C_Id);

新增外鍵 (FOREIGN KEY)

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(C_Id);

新增唯一約束 (UNIQUE)

ALTER TABLE customers
ADD CONSTRAINT uq_email UNIQUE (Email);

新增檢查約束 (CHECK)

ALTER TABLE customers
ADD CONSTRAINT chk_discount CHECK (Discount >= 0 AND Discount <= 100);

新增預設值 (DEFAULT)

SQL Server：

ALTER TABLE customers
ADD CONSTRAINT df_status DEFAULT 'active' FOR Status;

MySQL：

ALTER TABLE customers
ALTER Status SET DEFAULT 'active';

PostgreSQL：

ALTER TABLE customers
ALTER COLUMN Status SET DEFAULT 'active';

刪除約束條件 (DROP CONSTRAINT)

刪除主鍵

MySQL：

ALTER TABLE customers
DROP PRIMARY KEY;

SQL Server / PostgreSQL：

ALTER TABLE customers
DROP CONSTRAINT pk_customers;

刪除外鍵

MySQL：

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

SQL Server / PostgreSQL：

ALTER TABLE orders
DROP CONSTRAINT fk_customer;

刪除唯一約束

MySQL：

ALTER TABLE customers
DROP INDEX uq_email;

SQL Server / PostgreSQL：

ALTER TABLE customers
DROP CONSTRAINT uq_email;

刪除預設值

MySQL：

ALTER TABLE customers
ALTER Status DROP DEFAULT;

SQL Server：

ALTER TABLE customers
DROP CONSTRAINT df_status;

PostgreSQL：

ALTER TABLE customers
ALTER COLUMN Status DROP DEFAULT;

設定欄位為 NOT NULL 或允許 NULL

設定為 NOT NULL

MySQL：

ALTER TABLE customers
MODIFY Email VARCHAR(100) NOT NULL;

SQL Server：

ALTER TABLE customers
ALTER COLUMN Email VARCHAR(100) NOT NULL;

PostgreSQL：

ALTER TABLE customers
ALTER COLUMN Email SET NOT NULL;

移除 NOT NULL (允許 NULL)

MySQL：

ALTER TABLE customers
MODIFY Email VARCHAR(100) NULL;

SQL Server：

ALTER TABLE customers
ALTER COLUMN Email VARCHAR(100) NULL;

PostgreSQL：

ALTER TABLE customers
ALTER COLUMN Email DROP NOT NULL;

新增索引 (ADD INDEX)

MySQL：

ALTER TABLE customers
ADD INDEX idx_name (Name);

SQL Server：

CREATE INDEX idx_name ON customers (Name);

PostgreSQL：

CREATE INDEX idx_name ON customers (Name);

實用範例

範例 1：建立完整的客戶資料表結構修改

-- 新增多個欄位
ALTER TABLE customers
ADD Email VARCHAR(100) NOT NULL,
ADD Created_At DATETIME DEFAULT CURRENT_TIMESTAMP,
ADD Updated_At DATETIME;

-- 新增唯一約束確保 Email 不重複
ALTER TABLE customers
ADD CONSTRAINT uq_customer_email UNIQUE (Email);

-- 新增索引加速查詢
ALTER TABLE customers
ADD INDEX idx_customer_name (Name);

範例 2：修改現有欄位

-- 增加 Address 欄位長度
ALTER TABLE customers
MODIFY Address VARCHAR(500);

-- 將 Phone 欄位設為必填
ALTER TABLE customers
MODIFY Phone VARCHAR(20) NOT NULL;

各資料庫語法比較表

注意事項

1. 備份資料： 在執行 ALTER TABLE 之前，建議先備份資料表，特別是刪除欄位或修改資料型別時。

2. 鎖定問題： 在大型資料表上執行 ALTER TABLE 可能會造成資料表鎖定，影響其他查詢操作。

3. 資料遺失風險： 縮小欄位長度或更改資料型別可能導致資料遺失或截斷。

4. 語法差異： 不同資料庫系統的語法可能有所不同，請參考各資料庫的官方文件。

5. 交易處理： 某些資料庫系統的 ALTER TABLE 操作無法在交易 (Transaction) 中回滾。