SSIS ETL项目中外键约束冲突求助（ISNULL设0报错）

问题现象 #

在ETL项目中，使用ISNULL(iddimempleado,0)将NULL值转为0时触发SSIS错误；若将其设为已存在的数值（如1），在检查约束开启状态下可正常运行。

错误日志 #

Error: 0xC0202009 at Carga Tabla Hecho, OLE DB Destination [2]: SSIS Error Code DTS_E_OLEDBERROR. 发生OLE DB错误，错误代码：0x80004005。

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "语句已终止。"。

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "INSERT语句与外键约束"fk_facventas_dimempleados"冲突，冲突发生在数据库"BikeZ_DW"的dbo.dimempleados表的'iddimempleado'列。"。

查询代码 #

SELECT 
    temp.DetalleVentaID, t.iddimtiempo, c.iddimcliente,  
    ISNULL(iddimempleado,0) AS iddimempleado, te.iddimterritorio, 
    p.iddimproducto, temp.Cantidad, temp.PrecioUnitario 
FROM 
    (SELECT
         d.DetalleVentaID, cantidad, precioUnitario, fecha, 
         clienteID, COALESCE(vendedorID,0) AS vendedor_ID, 
         v.territorioID, ProductoID       
     FROM 
         ventas v 
     JOIN 
         DetalleVentas d ON (v.ventaid = d.ventaid)) temp          
JOIN 
    BikeZ_DW.dbo.dimtiempo t ON (temp.fecha = t.fecha)       
JOIN 
    BikeZ_DW.dbo.dimcliente c ON (temp.clienteID = c.idcliente)       
LEFT JOIN 
    BikeZ_DW.dbo.dimempleados e ON (temp.vendedor_ID = e.idempleado)       
JOIN 
    BikeZ_DW.dbo.dimterritorio te ON (temp.territorioID = te.idterritorio)       
JOIN 
    BikeZ_DW.dbo.dimproducto p ON (temp.productoID = P.IDPRODUCTO) 

数据仓库数据库定义 #

CREATE TABLE dimempleados (
    iddimempleado   INT IDENTITY(1,1) NOT NULL,
    idempleado      INT NOT NULL,
    genero          VARCHAR(1) NULL
    PRIMARY KEY (iddimempleado) );

CREATE TABLE facventas (
    idfactventas                    INT IDENTITY(1,1) NOT NULL,
    monto                           Money NULL,
    cantidad                        INT NULL,
    dimtiempo_iddimtiempo           INT NOT NULL,
    dimempleados_iddimempleado      INT NOT NULL,
    dimproducto_iddimproducto       INT NOT NULL,
    dimterritorio_iddimterritorio   INT NOT NULL,
    dimcliente_iddimcliente         INT NOT NULL,
    PRIMARY KEY (idfactventas,dimempleados_iddimempleado,dimterritorio_iddimterritorio, dimcliente_iddimcliente, dimproducto_iddimproducto, dimtiempo_iddimtiempo)
);

ALTER TABLE facventas
ADD CONSTRAINT fk_facventas_dimempleados
    FOREIGN KEY (dimempleados_iddimempleado)
    REFERENCES dimempleados(iddimempleado)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

原因分析 #

• 外键约束fk_facventas_dimempleados要求facventas.dimempleados_iddimempleado的值必须存在于dimempleados.iddimempleado中
• dimempleados的iddimempleado是IDENTITY(1,1)自增字段，默认起始值为1，不存在值为0的记录
• 当源数据中vendedorID为NULL时，子查询用COALESCE转为0，LEFT JOINdimempleados时因idempleado无0值导致iddimempleado为NULL，后续ISNULL将其转为0，插入时违反外键约束触发报错；若转为已存在的数值（如1），则符合约束要求，可正常运行

解决方案 #

方案1：添加默认维度记录（推荐） #

在dimempleados中添加一条代表“未知员工”或“无销售人员”的默认记录，用于关联无有效销售人员的订单：

-- 插入默认员工记录，iddimempleado会自动生成
INSERT INTO dimempleados(idempleado, genero) VALUES(0, 'U');

然后修改查询中的ISNULL逻辑，指向这条默认记录的iddimempleado值（假设生成的ID为5）：

ISNULL(iddimempleado,5) AS iddimempleado

方案2：调整源数据关联逻辑 #

修改子查询和JOIN逻辑，直接将vendedorID=0的记录关联到默认员工：

SELECT 
    temp.DetalleVentaID, t.iddimtiempo, c.iddimcliente,  
    ISNULL(e.iddimempleado, def.iddimempleado) AS iddimempleado, te.iddimterritorio, 
    p.iddimproducto, temp.Cantidad, temp.PrecioUnitario 
FROM 
    (SELECT
         d.DetalleVentaID, cantidad, precioUnitario, fecha, 
         clienteID, COALESCE(vendedorID,0) AS vendedor_ID, 
         v.territorioID, ProductoID       
     FROM 
         ventas v 
     JOIN 
         DetalleVentas d ON (v.ventaid = d.ventaid)) temp          
JOIN 
    BikeZ_DW.dbo.dimtiempo t ON (temp.fecha = t.fecha)       
JOIN 
    BikeZ_DW.dbo.dimcliente c ON (temp.clienteID = c.idcliente)       
LEFT JOIN 
    BikeZ_DW.dbo.dimempleados e ON (temp.vendedor_ID = e.idempleado)
-- 关联默认员工记录
CROSS JOIN (SELECT iddimempleado FROM dimempleados WHERE idempleado=0) def
JOIN 
    BikeZ_DW.dbo.dimterritorio te ON (temp.territorioID = te.idterritorio)       
JOIN 
    BikeZ_DW.dbo.dimproducto p ON (temp.productoID = P.IDPRODUCTO) 

方案3：修改外键字段允许NULL（不推荐） #

若业务场景允许无销售人员关联，可修改facventas的外键字段为允许NULL，但不符合数据仓库事实表通常需关联有效维度记录的设计原则：

ALTER TABLE facventas ALTER COLUMN dimempleados_iddimempleado INT NULL;

同时修改查询中的ISNULL为保留NULL：

iddimempleado AS iddimempleado -- 去掉ISNULL转换，保留NULL

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