SQL 資料表建立、修改與刪除完整操作指南

SQL Server 資料表 DDL 操作全面解析

資料表是關聯式資料庫的核心物件。掌握 CREATE TABLE、ALTER TABLE、DROP TABLE 的完整語法與選項，是資料庫開發與管理的基本功。

資料表類型分類

• 永久表：持久儲存，除非明確刪除
• 臨時表：會話結束或系統重啟自動清理
  • 區域臨時表 #table_name — 僅目前會話可見
  • 全域臨時表 ##table_name — 所有會話可見

臨時表名稱含 # 號，最大長度 20 字元（含 #）。

CREATE TABLE 建立資料表

完整語法架構

CREATE TABLE [database.[schema.]]table_name
(
    column_name data_type [IDENTITY(seed, increment)] 
        [NULL | NOT NULL] 
        [DEFAULT constant_expression]
        [PRIMARY KEY | UNIQUE]
        [CHECK (logical_expression)]
        [COLLATE collation_name],
    ...
    [table_constraint] ::= 
        [CONSTRAINT constraint_name] 
        {PRIMARY KEY | UNIQUE | FOREIGN KEY | CHECK} (column_list)
)

IDENTITY 自動編號欄位

• 每張表僅能有一個 IDENTITY 欄位
• 資料型別限制：int、bigint、smallint、tinyint、decimal、numeric
• decimal/numeric 型別不允許小數位
• 值由系統自動產生，不可手動更新，不允許 NULL
• seed 起始值，increment 遞增值，預設皆為 1

實戰範例

CREATE TABLE person
(
    person_id INT IDENTITY(1, 10) PRIMARY KEY,  -- 1, 11, 21, 31...
    name CHAR(8) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at DATETIME DEFAULT GETDATE(),
    status TINYINT DEFAULT 1 CHECK (status IN (0,1,2))
)

ALTER TABLE 修改資料表結構

核心操作類型

ALTER TABLE [database.[schema.]]table_name
{
    ADD column_definition | table_constraint
  | DROP CONSTRAINT constraint_name
  | ALTER COLUMN column_name new_data_type [NULL | NOT NULL]
  | ENABLE | DISABLE TRIGGER | CONSTRAINT
  | SWITCH PARTITION ...
}

常見操作範例

-- 新增欄位
ALTER TABLE person ADD country CHAR(2) NULL

-- 修改欄位型別 (需確保資料相容)
ALTER TABLE person ALTER COLUMN name VARCHAR(50) NOT NULL

-- 新增約束
ALTER TABLE person ADD CONSTRAINT chk_age CHECK (age >= 0)

-- 刪除約束
ALTER TABLE person DROP CONSTRAINT chk_age

-- 重新命名 (需 sp_rename)
EXEC sp_rename 'person', 'users'

DROP TABLE 刪除資料表

DROP TABLE [database.[schema.]]table_name [, ...n]

關鍵行為

• 刪除資料表結構、所有資料、索引、觸發器、約束、權限
• 若有外鍵參考，必須先刪除參考表的外鍵或先刪參考表
• 不可回復（除非有備份或在交易中 ROLLBACK）

安全刪除模式

-- 檢查相依性
SELECT OBJECT_NAME(parent_object_id) AS referencing_table
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('dbo.person')

-- 交易中刪除 (可回滾)
BEGIN TRAN
DROP TABLE person
-- ROLLBACK / COMMIT

最佳實踐建議

1. 明確指定 Schema：避免預設 schema 造成歧義
2. 命名規範：PK_表名、FK_表名_參考表、IX_表名_欄位、CK_表名_規則
3. 預設值優於觸發器：簡單預設邏輯用 DEFAULT，複雜邏輯才用觸發器
4. DDL 變更上線流程：評估鎖定影響 → 低峰期執行 → 驗證應用相容性
5. 大表 ALTER COLUMN：考慮建新欄 → 遷移資料 → 交換欄位 → 刪舊欄，避免長時間鎖表