垦荒学园
  • 中文

      • PostgreSQL 修改表

      概述

      ALTER TABLE 用于修改现有表的结构，包括添加、删除或修改列，添加或删除约束等。

      添加列

      -- 添加单个列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- 添加带约束的列
ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL;

-- 添加多个列
ALTER TABLE users 
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP;

      删除列

      -- 删除单个列
ALTER TABLE users DROP COLUMN phone;

-- 删除多个列
ALTER TABLE users 
    DROP COLUMN phone,
    DROP COLUMN fax;

-- 级联删除（删除依赖对象）
ALTER TABLE users DROP COLUMN email CASCADE;

-- 如果存在则删除
ALTER TABLE users DROP COLUMN IF EXISTS phone;

      修改列

      重命名列

      -- 重名列列
ALTER TABLE users RENAME COLUMN name TO full_name;

-- 重命名多个列
ALTER TABLE users RENAME COLUMN addr TO address;
ALTER TABLE users RENAME COLUMN tel TO phone;

      更改数据类型

      -- 更改列类型
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;

-- 使用USING转换数据
ALTER TABLE users 
    ALTER COLUMN price TYPE NUMERIC(10,2) 
    USING price::NUMERIC(10,2);

-- 更改字符串长度
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);

      设置/删除默认值

      -- 设置默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置当前时间戳为默认值
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

      设置/删除NOT NULL

      -- 设置NOT NULL约束
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 删除NOT NULL约束
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

      添加约束

      主键约束

      -- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加命名主键
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);

-- 添加复合主键
ALTER TABLE order_items 
    ADD PRIMARY KEY (order_id, product_id);

      外键约束

      -- 添加外键
ALTER TABLE orders 
    ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加命名外键
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加带级联的外键
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

      唯一约束

      -- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加命名唯一约束
ALTER TABLE users 
    ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 添加复合唯一约束
ALTER TABLE products 
    ADD CONSTRAINT products_name_category_unique 
    UNIQUE (name, category_id);

      检查约束

      -- 添加检查约束
ALTER TABLE products 
    ADD CHECK (price > 0);

-- 添加命名检查约束
ALTER TABLE products 
    ADD CONSTRAINT products_price_check 
    CHECK (price > 0 AND price < 1000000);

-- 添加复杂检查约束
ALTER TABLE users 
    ADD CONSTRAINT users_age_check 
    CHECK (age >= 18 AND age <= 120);

      删除约束

      -- 删除主键
ALTER TABLE users DROP CONSTRAINT users_pkey;

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_orders_users;

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT users_email_unique;

-- 删除检查约束
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- 级联删除约束
ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;

      重命名表

      -- 重命名表
ALTER TABLE users RENAME TO customers;

-- 重命名表（如果存在）
ALTER TABLE IF EXISTS old_table RENAME TO new_table;

      更改表所有者

      -- 更改表所有者
ALTER TABLE users OWNER TO new_owner;

-- 更改多个表的所有者
ALTER TABLE users OWNER TO admin;
ALTER TABLE orders OWNER TO admin;

      更改表空间

      -- 移动表到不同的表空间
ALTER TABLE users SET TABLESPACE fast_storage;

-- 移动表及其索引
ALTER TABLE users SET TABLESPACE fast_storage;

      实际示例

      示例1：用户表重构

      -- 添加新列
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
ALTER TABLE users ADD COLUMN login_count INTEGER DEFAULT 0;

-- 修改现有列
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 添加检查约束
ALTER TABLE users ADD CONSTRAINT users_age_check CHECK (age >= 18);

-- 重命名列
ALTER TABLE users RENAME COLUMN addr TO address;

      示例2：订单表优化

      -- 添加外键约束
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

-- 添加状态检查
ALTER TABLE orders 
    ADD CONSTRAINT orders_status_check 
    CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- 添加金额检查
ALTER TABLE orders 
    ADD CONSTRAINT orders_amount_check 
    CHECK (total_amount >= 0);

-- 添加时间戳
ALTER TABLE orders 
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

      示例3：产品表扩展

      -- 添加新字段
ALTER TABLE products 
    ADD COLUMN description TEXT,
    ADD COLUMN image_url VARCHAR(500),
    ADD COLUMN is_active BOOLEAN DEFAULT true;

-- 修改价格列
ALTER TABLE products 
    ALTER COLUMN price TYPE NUMERIC(10,2),
    ALTER COLUMN price SET NOT NULL;

-- 添加约束
ALTER TABLE products 
    ADD CONSTRAINT products_price_check CHECK (price > 0),
    ADD CONSTRAINT products_stock_check CHECK (stock >= 0);

-- 添加唯一约束
ALTER TABLE products 
    ADD CONSTRAINT products_sku_unique UNIQUE (sku);

      最佳实践

      1. 备份数据：在修改表结构前始终备份数据
      2. 使用事务：在事务中执行 ALTER TABLE 操作
      3. 命名约束：为约束指定有意义的名称
      4. 测试环境：先在测试环境中测试修改
      5. 避免锁表：注意某些操作会锁定整个表
      6. 使用 IF EXISTS：使用 IF EXISTS 避免错误

      注意事项

      • ALTER TABLE 操作可能会锁定表
      • 某些修改需要重写整个表
      • 添加 NOT NULL 列时需要提供默认值或确保无 NULL 值
      • 外键约束会影响性能
      • 大表的结构修改可能需要很长时间

      小结

      ALTER TABLE 是 PostgreSQL 中修改表结构的强大工具：

      • 添加/删除列：扩展或精简表结构
      • 修改列属性：更改数据类型、默认值、NULL 约束
      • 管理约束：添加或删除主键、外键、唯一和检查约束
      • 重命名：重命名表和列
      • 其他操作：更改所有者、表空间等

      合理使用 ALTER TABLE 可以灵活地调整数据库结构以适应业务需求。