PostgreSQL+Sequelize：认证类型为password时禁止密码列空值

一、为什么你的CHECK约束未生效？ #

你写的CHECK约束逻辑完全搞反了：它要求仅当authentication_type_id是指定值且password非空时才满足约束，但实际需求是当authentication_type_id是指定值时，password必须非空；其他情况不做限制。正确的CHECK约束条件是以下两种等价写法之一：

-- 写法1：排除"认证类型是password且password为空"的非法情况
CHECK (NOT (authentication_type_id = 'c1cc0489-4740-4dca-9d63-14e4c26093ad' AND password IS NULL))

-- 写法2：逻辑或，满足任一条件即可
CHECK (authentication_type_id != 'c1cc0489-4740-4dca-9d63-14e4c26093ad' OR password IS NOT NULL)

二、能否用CHECK约束实现需求？ #

分两种场景判断：

• 可以用CHECK约束：如果authentication_type表中name为"password"的记录ID固定不变，直接使用上面修正后的CHECK约束即可，这种方式性能高、实现简单。
• 不能用CHECK约束：如果需要基于authentication_type表的name字段动态判断（比如后续可能新增其他name为"password"的记录，或该记录的ID可能变更），因为PostgreSQL的CHECK约束只能引用本表字段，无法跨表查询其他表的数据，此时必须使用触发器。

三、CHECK约束与触发器的区别 #

四、Sequelize中的实现方式 #

1. 使用CHECK约束（固定ID场景） #

方式一：通过模型constraints属性定义

module.exports = (sequelize, DataTypes) => {
  const Accounts = sequelize.define('Accounts', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false
    },
    password: DataTypes.STRING,
    authentication_type_id: {
      type: DataTypes.UUID,
      allowNull: false,
      references: { model: 'authentication_type', key: 'id' }
    },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW
    }
  }, {
    tableName: 'accounts',
    timestamps: false,
    constraints: [
      {
        type: 'CHECK',
        name: 'check_password_not_null_when_auth_type_password',
        condition: sequelize.literal(`NOT (authentication_type_id = 'c1cc0489-4740-4dca-9d63-14e4c26093ad' AND password IS NULL)`)
      }
    ]
  });
  return Accounts;
};

方式二：使用addConstraint方法

// 在模型定义后调用
Accounts.addConstraint('accounts', {
  fields: ['authentication_type_id', 'password'],
  type: 'check',
  name: 'check_password_not_null_when_auth_type_password',
  where: {
    [sequelize.Op.or]: [
      { authentication_type_id: { [sequelize.Op.ne]: 'c1cc0489-4740-4dca-9d63-14e4c26093ad' } },
      { password: { [sequelize.Op.not]: null } }
    ]
  }
});

2. 使用触发器（动态关联name场景） #

需要先创建触发器函数，再绑定触发器到accounts表，在Sequelize中通过sequelize.query执行SQL：

module.exports = (sequelize, DataTypes) => {
  const Accounts = sequelize.define('Accounts', {
    // 字段定义同上
  });

  // 同步后创建触发器及函数
  Accounts.afterSync(async () => {
    // 创建触发器函数
    await sequelize.query(`
      CREATE OR REPLACE FUNCTION check_password_for_auth_type()
      RETURNS TRIGGER AS $$
      BEGIN
        IF (SELECT name FROM authentication_type WHERE id = NEW.authentication_type_id) = 'password' AND NEW.password IS NULL THEN
          RAISE EXCEPTION 'Password cannot be null when authentication type is password';
        END IF;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // 创建触发器
    await sequelize.query(`
      CREATE TRIGGER trigger_check_password_before_insert_update
      BEFORE INSERT OR UPDATE ON accounts
      FOR EACH ROW EXECUTE FUNCTION check_password_for_auth_type();
    `);
  });

  return Accounts;
};

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