如何为现有users表添加带UNIQUE和NOT NULL约束的username列及迁移方案

一、原生SQL操作步骤 #

直接添加NOT NULL且无默认值的字段会失败——因为现有数据没有对应值，数据库会触发约束校验错误。必须分三步操作：

MySQL 示例 #

-- 1. 先添加允许为空的username字段，同时加上UNIQUE约束
ALTER TABLE users ADD COLUMN username VARCHAR(50) UNIQUE;

-- 2. 给现有数据批量填充唯一值（用主键id拼接，保证唯一性）
UPDATE users SET username = CONCAT('user_', id);

-- 3. 最后修改字段为NOT NULL约束
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL UNIQUE;

PostgreSQL 示例 #

-- 1. 添加允许为空的username字段及UNIQUE约束
ALTER TABLE users ADD COLUMN username VARCHAR(50) UNIQUE;

-- 2. 填充唯一值（将id转为文本拼接）
UPDATE users SET username = 'user_' || id::TEXT;

-- 3. 设置NOT NULL约束
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

二、对现有数据的影响 #

• 直接跳过填充步骤添加NOT NULL字段会报错，数据库不允许现有行存在空值的约束字段。
• 填充数据时必须保证每个username唯一，否则UNIQUE约束会触发错误——用主键id拼接是最稳妥的方式，因为主键本身具备唯一性。
• 生产环境操作时，建议在低峰期执行，大表要分批更新（比如按id分段），避免长时间锁表影响业务；如果有并发写入，最好暂时暂停写入，防止新插入的数据没有username导致后续约束设置失败。

三、SQLAlchemy + Alembic 迁移实现 #

1. 更新ORM模型 #

在你的User模型中新增username字段：

# models.py
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 新增字段，指定非空和唯一约束
    username = Column(String(50), nullable=False, unique=True)

2. 生成初始迁移脚本 #

在项目根目录执行命令生成迁移文件：

alembic revision --autogenerate -m "Add username column with UNIQUE and NOT NULL constraints"

3. 修改自动生成的迁移脚本 #

自动生成的脚本会直接尝试添加NOT NULL字段，这会报错，必须调整为三步逻辑：

# versions/[随机版本号]_add_username_column.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    # 1. 先添加允许为空的username字段，创建唯一约束
    op.add_column('users', sa.Column('username', sa.String(length=50), nullable=True))
    op.create_unique_constraint('uq_users_username', 'users', ['username'])
    
    # 2. 给现有数据填充唯一username值（根据数据库类型选择对应语句）
    conn = op.get_bind()
    # MySQL 写法
    conn.execute(sa.text("UPDATE users SET username = CONCAT('user_', id)"))
    # PostgreSQL 写法请替换成下面这行
    # conn.execute(sa.text("UPDATE users SET username = 'user_' || id::TEXT"))
    
    # 3. 修改字段为非空约束
    op.alter_column('users', 'username', nullable=False)

def downgrade():
    # 回滚操作：删除约束和字段
    op.drop_constraint('uq_users_username', 'users', type_='unique')
    op.drop_column('users', 'username')

4. 执行迁移 #

运行命令完成字段添加：

alembic upgrade head

迁移注意事项 #

• 操作前务必备份数据库，防止数据丢失。
• 大表迁移时，把UPDATE语句拆成分批执行（比如按id区间分段），避免锁表时间过长。
• 生产环境迁移时，建议暂停应用的写入操作，或者确保应用在迁移期间不会插入新数据——否则新数据会因没有username导致后续设置NOT NULL约束失败。

内容的提问来源于stack exchange，提问作者berinaniesh