SQL Server中nvarchar转varbinary的校验及CHECK约束冲突问题求助

问题原因 #

• 编码不匹配：你当前CHECK约束中写的0x4C4533、0x475433是单字节编码的varchar类型'LE3'、'GT3'转换得到的二进制值，而你迁移的原始字段是nvarchar类型（Unicode编码，每个字符占2字节），直接转换为varbinary得到的是双字节的二进制结果，和约束中的值完全不匹配，因此触发冲突。你可以执行SELECT CAST(N'LE3' AS VARBINARY(100))验证，nvarchar类型的N'LE3'转换后的值为0x4C0045003300，和你约束中的0x4C4533不一致。
• 默认值未纳入校验范围：你给studentFamSize字段设置了默认值0，但当前CHECK约束中没有将0列为合法值，插入数据未显式给该字段赋值时也会触发约束冲突。

解决方法 #

你可以根据业务需求选择以下任意一种方案：

方案1：修改CHECK约束匹配nvarchar的二进制结果 #

直接调整约束中的二进制值为nvarchar类型转换后的正确值，同时补全默认值的校验规则：

CONSTRAINT CHK_studentFamSize 
    CHECK (
        studentFamSize = 0x4C0045003300 -- 对应N'LE3'
        OR studentFamSize = 0x470054003300 -- 对应N'GT3'
        OR studentFamSize = 0 -- 兼容字段默认值
    )

如果业务不需要默认值0，你也可以直接删除字段定义中的DEFAULT 0配置，无需在约束中增加0的校验。

方案2：修改迁移转换逻辑适配现有约束 #

如果要保留当前的CHECK约束不变，可以在迁移时先将nvarchar字段转为varchar再转varbinary，让转换结果匹配约束值：

CAST(CAST(famsize AS VARCHAR(100)) AS VARBINARY(100))

该方案仅适合原始famsize字段仅包含ASCII字符的场景，不会出现乱码问题。

方案3：使用更稳妥的字符串对比逻辑 #

避免硬编码二进制值容易出错的问题，直接在CHECK约束中将二进制字段转回nvarchar做对比：

CONSTRAINT CHK_studentFamSize 
    CHECK (
        CAST(studentFamSize AS NVARCHAR(10)) IN (N'LE3', N'GT3')
        OR studentFamSize = 0
    )

该方案可规避编码差异导致的校验错误，后续调整合法值时也无需重新计算二进制编码。

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