主页 / dba / 问题 / 252247
Accepted
Sarov
Asked: 2019-10-31 11:47:43 +0800 CST

对可为空的软删除标志设置唯一约束是否有意义？

  • 772

所以我们目前有以下内容：

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK: DELETED = 0 OR DELETED = 1

现在，要求允许无限删除记录，但每个日期+公司只允许一条未删除记录。

我建议将架构更改为：

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: ACTIVE (Nullable INTEGER)
CHECK: ACTIVE = 1 OR ACTIVE IS NULL
UNIQUE: START, COMPANYID, ACTIVE

虽然我的同事认为这“太过分了”，但我们应该只依赖应用程序中的唯一性检查。

这里有普遍接受的最佳实践吗？

database-design unique-constraint

1 个回答

  1. Best Answer

    您没有提及您所指的 DBMS，但是您建议的唯一约束不适用于那里的几个，因为 null 不等于 null

    create table t (x int not null, y int, unique(x,y));
insert into t (x) values (1);
insert into t (x) values (1);

Postgres12
Does not violate the constraint

Oracle18c
Violates constraint

Db2 Developer C 11.1
Does not accept nullable columns in unique constraint

Firebird 3.0
Violates constraint

MariaDB 10.4
Does not violate the constraint

MySQL 8.0
Does not violate the constraint

SQLlite 3.27
Does not violate the constraint

SQLServer 2017
Violates constraint

    在DB<>Fiddle测试

    所以，一般来说，你的 UNIQUE 约束不起作用。考虑将 ACTIVE 更改为

    ACTIVE SMALLINT NOT NULL,
CHECK (ACTIVE BETWEEN 0 AND 1),
UNIQUE (START, COMPANYID, ACTIVE)

    虽然我不相信某个 COMPANYID 在特定开始时既可以是活跃的也可以是不活跃的，但是我不知道您的业务。

    编辑：鉴于问题中的新信息，这样的事情可能是可能的（我现在无法访问系统 i 的文档，所以我不知道是否存在生成的列或表达式的约束）

    create table t 
( a int not null generated always as identity
, x int not null
, y int
, z int not null generated always as ( 
    case when y is null then -1*a else y end 
)
, unique(x,z)
, check (y is null or y = 1)
);

insert into t(x) values (1); -- ok
insert into t(x) values (1); -- ok
insert into t(x,y) values (1,1); -- ok
insert into t(x,y) values (1,1); -- fails because of constraint violation.
    • 2

相关问题