Sequelize插入时自动添加不存在列building_building_id报错求助

使用Sequelize 6.13.0向PostgreSQL的property表插入数据，执行代码如下：

await Property.create(
    {
        clikalia_id: 1,
        property_type_id: PROPERTY_TYPES.EDIFICIO,
        street: newBuilding.street,
        number: newBuilding.number,
        zipcode: newBuilding.zipcode,
        location: point,
        owner_type: newBuilding.owner_type,
        uv_crm_id: newBuilding.uv_crm_id,
        easybroker_id: newBuilding.easybroker_id,
        uv: newBuilding.uv,
        stair: 0,
        door: 0,
        block: 0,
        inner_number: 0,
        floor: 0,
    }
);

执行时Sequelize会自动添加building_building_id列，因该列在数据库中不存在抛出异常。重启服务器后问题暂时消失，但会再次出现。需要解决该问题，避免频繁重启服务器，怀疑是Building表模型定义有误。

Building模型 #

const Building = sequelizeSells.define(
    'building',
    {
        building_id: {
            type: DataTypes.UUID,
            primaryKey: true,
            references: {
                model: Property,
                key: 'id',
                deferrable: Deferrable.INITIALLY_IMMEDIATE,
            },
        },
        property_id: {
            type: DataTypes.UUID,
            primaryKey: true,
            references: {
                model: Property,
                key: 'id',
                deferrable: Deferrable.INITIALLY_IMMEDIATE,
            },
        },
    },
    {
        freezeTableName: true,
        timestamps: false,
        createdAt: false,
        updatedAt: false,
    }
);

export default Building;

Property模型 #

const Property = sequelizeSells.define(
    'property',
    {
        id: {
            type: DataTypes.UUID,
            primaryKey: true,
            defaultValue: DataTypes.UUIDV4,
        },
        clikalia_id: {
            type: DataTypes.INTEGER,
        },
        property_type_id: {
            type: DataTypes.INTEGER,
        },
        street: {
            type: DataTypes.STRING,
        },
        number: {
            type: DataTypes.STRING,
        },
        stair: {
            type: DataTypes.STRING,
        },
        door: {
            type: DataTypes.STRING,
        },
        block: {
            type: DataTypes.STRING,
        },
        inner_number: {
            type: DataTypes.STRING,
        },
        uv: {
            type: DataTypes.STRING,
        },
        floor: {
            type: DataTypes.INTEGER,
        },
        location: {
            type: DataTypes.GEOGRAPHY,
        },
        zipcode: {
            type: DataTypes.STRING,
        },
        owner_type: {
            type: DataTypes.STRING,
        },
        created_at: {
            type: DataTypes.TIME,
        },
        updated_at: {
            type: DataTypes.TIME,
        },
        uv_crm_id: {
            type: DataTypes.NUMBER,
        },
        easybroker_id: {
            type: DataTypes.STRING,
        },
    },
    {
        freezeTableName: true,
        timestamps: false,
        createdAt: true,
        updatedAt: true,
    }
);

export default Property;

1. 修正Building模型的主键与外键定义 #

当前Building模型中building_id作为主键同时引用Property的id，这种配置会让Sequelize错误推断关联关系，自动生成额外的外键列。正确的做法是将building_id设为自身独立主键，property_id作为外键关联Property：

const Building = sequelizeSells.define(
    'building',
    {
        building_id: {
            type: DataTypes.UUID,
            primaryKey: true,
            defaultValue: DataTypes.UUIDV4, // 独立生成主键，不再引用Property
        },
        property_id: {
            type: DataTypes.UUID,
            allowNull: false,
            references: {
                model: 'property', // 使用表名确保引用正确，避免模型加载顺序问题
                key: 'id',
                deferrable: Deferrable.INITIALLY_IMMEDIATE,
            },
        },
    },
    {
        freezeTableName: true,
        timestamps: false,
    }
);

export default Building;

2. 显式定义模型关联关系 #

在模型初始化时添加关联定义（通常在专门的关联文件或模型的associate方法中），明确Property与Building的关系：

// 假设在关联配置文件中
Building.belongsTo(Property, { foreignKey: 'property_id', targetKey: 'id' });
Property.hasOne(Building, { foreignKey: 'property_id', sourceKey: 'id' });

3. 插入时显式指定字段 #

在Property.create调用中添加fields选项，强制Sequelize只插入指定字段，避免自动添加额外列：

await Property.create(
    {
        clikalia_id: 1,
        property_type_id: PROPERTY_TYPES.EDIFICIO,
        street: newBuilding.street,
        number: newBuilding.number,
        zipcode: newBuilding.zipcode,
        location: point,
        owner_type: newBuilding.owner_type,
        uv_crm_id: newBuilding.uv_crm_id,
        easybroker_id: newBuilding.easybroker_id,
        uv: newBuilding.uv,
        stair: 0,
        door: 0,
        block: 0,
        inner_number: 0,
        floor: 0,
    },
    { 
        fields: [
            'clikalia_id', 'property_type_id', 'street', 'number', 
            'zipcode', 'location', 'owner_type', 'uv_crm_id', 
            'easybroker_id', 'uv', 'stair', 'door', 'block', 
            'inner_number', 'floor'
        ] 
    }
);

4. 确保模型加载顺序正确 #

先加载Property模型，再加载Building模型，避免循环引用导致的缓存异常——这也是重启服务器暂时解决问题的原因（重启后加载顺序可能临时正确）。

内容的提问来源于stack exchange，提问作者Jorge López