时间戳

首页 > 解决方案 > 我可以使用模式约束来强制执行唯一性吗？

sql - 我可以使用模式约束来强制执行唯一性吗？

问题描述

假设我有两个相关的实体，例如。一个项目和一个过滤器。过滤器可以是：

每个过滤器都可以命名，我想确保过滤器具有唯一的名称。然而，唯一性取决于过滤器是共享的还是私有的。如果过滤器是共享的，则其名称在所有共享过滤器中必须是唯一的，并且任何私有过滤器都不能重复使用该名称。如果过滤器是私有的，那么它在其项目中必须是唯一的，即。两个不同的项目可以有两个同名的私有过滤器。

我要将过滤器数据存储在数据库表中，并且我想添加一个约束来强制执行这些命名规则。是否可以构造一个复合键来做到这一点？

我想象过滤表上有两列：

共享过滤器将有一个名称但没有项目 ID，而私有过滤器将有一个名称和它所属项目的 ID。

我可以构造一个约束来强制执行此操作吗？

对我的方法有什么想法吗？我是在正确的轨道上还是有更好的方法？

标签： sqlconstraints

解决方案

我认为仅使用约束不可能做你想做的事。

我能够设计这张桌子：

create table filters (
        filter_id int auto_increment primary key,
        filter_name varchar(20) not null,
        project_id int,
        is_shared bool as (case when project_id is null then true else null end),
        unique key (filter_name, project_id),
        unique key (filter_name, is_shared)
);

这强制了共享过滤器必须是唯一的约束，并且私有过滤器必须在同一项目的其他过滤器中是唯一的。

即以下过滤器成功：

insert into filters set filter_name = 'shared1', project_id = null; -- OK
insert into filters set filter_name = 'shared2', project_id = null; -- OK
insert into filters set filter_name = 'private1', project_id = 1;   -- OK
insert into filters set filter_name = 'private2', project_id = 1;   -- OK
insert into filters set filter_name = 'private1', project_id = 2;   -- OK
insert into filters set filter_name = 'private2', project_id = 2;   -- OK

但是这些都失败了，正确地：

insert into filters set filter_name = 'shared1', project_id = null; -- DUPLICATE
insert into filters set filter_name = 'private1', project_id = 1;   -- DUPLICATE
insert into filters set filter_name = 'private1', project_id = 2;   -- DUPLICATE

但是，我无法解决的棘手部分是使私有过滤器名称与共享过滤器名称冲突，而不是与来自不同项目的私有过滤器名称冲突。

insert into filters set filter_name = 'shared1', project_id = 1;
insert into filters set filter_name = 'private1', project_id = null;

我们不能只使 filter_name 唯一，因为这会使来自不同项目的私有过滤器相互冲突。

unique key (filter_name) -- WRONG

我还研究了创建一个VIEW WITH CHECK OPTION，但它需要使用自联接或 NOT EXISTS 子查询，这两种情况都意味着视图不支持 WITH CHECK OPTION。

我可以用 MySQL 5.7 中的触发器来做到这一点：

delimiter //
create trigger it1 before insert on filters
for each row begin
  if exists (select * from filters as f2 where f2.filter_name = NEW.filter_name and f2.project_id is null) then
    signal sqlstate '45000' 
    set message_text = 'filter conflicts with an existing shared filter';
  end if;
  if exists (select * from filters as f2 where f2.filter_name = NEW.filter_name and NEW.project_id is null) then
    signal sqlstate '45000' 
    set message_text = 'filter conflicts with an existing private filter';
  end if;
  if exists (select * from filters as f2 where f2.filter_name = NEW.filter_name and f2.project_id = NEW.project_id) then
    signal sqlstate '45000' 
    set message_text = 'filter conflicts with an existing private filter in the same project';
  end if;
end//
delimiter ;

推荐阅读