Oracle含DATE字段的复合主键约束未生效问题咨询

问题成因 #

• Oracle的DATE类型存储精度包含年、月、日、时、分、秒，并非仅存储日期到天维度。
• 查询结果中两条记录的DOB字段均显示为15-JUL-22，是因为当前会话默认的日期格式参数NLS_DATE_FORMAT配置为DD-MON-RR，仅渲染展示到日级别的值，时分秒部分被隐藏未显示。
• 两次插入操作调用SYSDATE获取的时间存在秒级差值，两条记录的DOB字段实际存储值并不相等，因此(SSN,DOB)的复合主键组合满足唯一性要求，主键约束本身是正常生效的，不存在失效问题。

验证方式 #

执行以下SQL调整会话级日期显示格式，展示DATE类型的完整精度值，即可看到两条记录DOB字段的实际差异：

-- 设置日期格式展示到秒级
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- 重新查询表数据
SELECT * FROM USER_TABLE1;

查询返回结果中会明确显示两条记录的DOB时分秒不一致，并非重复主键值。

适配方案 #

如果业务上要求以天为单位判定DOB字段的唯一性，插入数据时需要使用TRUNC()函数对时间做截断处理，示例：

-- 截断到日级别，同一天插入的DOB值完全一致，会正常触发主键冲突校验
INSERT INTO USER_TABLE1 VALUES('SSN1', TRUNC(SYSDATE), 'User1');

测试复现SQL #

CREATE TABLE USER_TABLE1 (
          SSN                       VARCHAR2(10 CHAR)           NOT NULL,
          DOB                       DATE                        NOT NULL,
          USER_NAME                 VARCHAR2(50)                NOT NULL,
          constraint USER_TABLE1_PK primary key(SSN,DOB)
);
        
-- 间隔1秒以上执行两次插入
insert into USER_TABLE1 values('SSN1',sysdate,'User1');
insert into USER_TABLE1 values('SSN1',sysdate,'User1');
        
-- 默认格式查询仅显示日期到天，会误以为值重复
select * from USER_TABLE1;

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