如何在父表上创建也约束子表的约束？

Question

我不确定如何表达这个问题，所以我将说明表格并解释我想要实现的目标。

-- static table of the entity classes supported by the application
create table entity_type (
  id   integer     not null auto_increment,
  name varchar(30) not null,

  primary key(id)
);

-- static table of statuses supported by the application
create table entity_status (    
  id   integer     not null auto_increment,
  name varchar(30) not null,

  primary key(id)
);

-- table of valid combinations 
create table entity_type_entity_status_link (    
  entity_type_id   integer not null,
  entity_status_id integer not null,

  unique key(entity_type_id, entity_status_id),
  foreign key(entity_type_id) references entity_type(id),
  foreign key(entity_status_id) references entity_status(id),
);

-- The tables where user types and statuses are defined
create table user_type (    
  id             integer     not null auto_increment,
  name           varchar(30) not null,

  entity_type_id integer not null,
  primary key(id),
  foreign key(entity_type_id) references entity_type(id)
);

create table user_status (    
  id             integer     not null auto_increment,
  name           varchar(30) not null,

  entity_status_id integer not null,
  primary key(id),
  foreign key(entity_status_id) references entity_status(id)
);

-- table of valid pairs
create table user_type_user_status_link (    
  user_type_id   integer not null,
  user_status_id integer not null,

  unique key(user_type_id, user_status_id),
  foreign key(user_type_id) references user_type(id),
  foreign key(user_status_id) references user_status(id),
);

这些表背后的基本前提是系统支持核心类型和状态，并且用户能够创建自己的用户类型和从中派生的状态。

我的问题是，我看不到在 user_type_user_status_link 表上创建任何数据库约束的方法，以确保您无法插入父实体类型 - 实体状态本身无效的 file_type - file_status 对。或者这是必须通过触发器来完成的事情。

Answer 1

这些表背后的基本前提是系统支持核心
类型和状态，用户可以创建自己的用户类型
以及由此衍生的雕像。

尽管从表面上看这听起来像是一个值得称赞的目标，但其效果是将数据库设计委托给用户。数据库设计，因为您希望将外键引用设置为 entity_type_entity_status_link 中的行子集，这意味着每个子集都是事实上的未命名表。

这种方法永远不会有好结果。

您开发的是“一个真实的查找表”。 Google 发现 OTLT 是一种反模式的原因有很多。

最好的解决方案是在表中对真实事物进行建模。 （实体不是真实的事物。它是真实事物的抽象。）类似于

create table file_status (
  file_status varchar(30) primary key
);

或

create table file_status (
  file_status_id integer primary key,
  file_status varchar(30) not null unique
);

的东西可以很好地用于文件状态。

对于第二种情况，您可以将外键引用设置为 ID 号（节省空间，需要额外的联接）或状态文本（占用更多空间，消除联接）。请注意，您需要对状态文本进行唯一约束；您的原始设计允许用户多次输入相同的文本。 （您最终可能会得到 30 行，其中 entity_type.name 是“文件”。

Answer 2

您应该为此使用触发器。
MySQL 不支持会阻止您想要的形式的约束。