使用 ALTER 修改 SQL 表

Source: Dev.to

将列设为 NOT NULL

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

添加唯一约束

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

添加检查约束

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);

设置默认值

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

添加带约束的列

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);

管理带 ON DELETE CASCADE 的外键

-- Remove the existing foreign key constraint
ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

-- Add a new foreign key with cascade delete
ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;

删除检查约束

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

查看表定义

\d accounts;

添加复合唯一约束

ALTER TABLE payments
ADD CONSTRAINT unique_user_transaction
UNIQUE (user_id, transaction_id);