数据库表中的行限制

Question

我有三个表：

BallId  Color

ball_1  red
ball_2  red
ball_3  blue
ball_4  green
ball_5  green

.......

.

BoxId  Color

box_1  green
box_2  green
box_3  red
.......

。

BoxId  BallId

box_1  ball4
box_1  ball5
box_3  ball2

我想在 BoxId、BallId 表上强制颜色关系，是否可以示意性地实现？

Answer 1

我不太确定你的最终目标是什么。

如果您只是想确保底部表中的 BoxId 和 BallId 必须存在于顶部两个表中，那么您可以使用 FOREIGN KEY（又名“引用完整性”） ）。

--- 编辑 ---

根据其他评论/响应，我发现您实际上希望确保通过第三个表连接的 2 行始终具有相同的颜色，但断开连接的行可以还是有自己的颜色的。

如果是这样，那么您可以像这样“滥用”键：

Ball:
    BallId PK, AK1
    Color  AK1

Box:
    BoxId  PK, AK1
    Color  AK1

BallInBox
    BallId PK
    BoxId  PK
    Color
    FK (BallId, Color) references Ball
    FK (BoxId, Color) references Box

这是实际的 DDL SQL：

delimiter $

CREATE TABLE Ball (
  BallId varchar(45) NOT NULL,
  Color varchar(45),
  PRIMARY KEY (BallId),
  UNIQUE KEY Ball_AK1 (BallId, Color)
)$

CREATE TABLE Box (
  BoxId varchar(45) NOT NULL,
  Color varchar(45),
  PRIMARY KEY (BoxId),
  UNIQUE KEY Box_AK1 (BoxId, Color)
)$

CREATE TABLE BallInBox (
  BallId varchar(45) NOT NULL,
  BoxId varchar(45) NOT NULL,
  Color varchar(45),
  PRIMARY KEY (BallId, BoxId),
  CONSTRAINT BallInBox_FK1 FOREIGN KEY (BallId, Color) REFERENCES Ball (BallId, Color),
  CONSTRAINT BallInBox_FK2 FOREIGN KEY (BoxId, Color) REFERENCES Box (BoxId, Color)
)$

顺便说一句，这允许在“基”表和“连接”表中使用 NULL 颜色。如果这不是您想要的，那么添加 NOT NULL 约束很容易。

Answer 2

一种方法（尽管它不是严格的“示意图”）是在第三个表上有一个插入触发器，它检查正在输入的球/框的颜色，如果它们不相同，则抛出异常

Answer 3

可以使用复合外键通过数据库来完成此操作：

create table ball
(id int unsigned not null primary key auto_increment,
color varchar(10)) engine=InnoDB;

create table box
(id int unsigned not null primary key auto_increment,
color varchar(10)) engine=InnoDB;

create table boxBallRule    
(ballId int unsigned not null,
boxId int unsigned not null,
PRIMARY KEY (ballId,boxId),
CONSTRAINT `boxBallRule_box_fk1` FOREIGN KEY (boxId) references `box` (id),
CONSTRAINT `boxBallRule_ball_fk1` FOREIGN KEY (ballId) references `ball` (id)
) engine=InnoDB;

create table boxBall
(id int unsigned primary key auto_increment not null,
ballId int unsigned not null,
boxId int unsigned not null,
CONSTRAINT `boxBallColorRule_fk1` FOREIGN KEY (ballId,boxId) references boxBallRule(ballId,boxId)
) engine=InnoDB;

然后您可以将哪些球允许放入 boxBallRule 表中的哪个盒子中。任何不符合“允许”框与球关系的 boxBall 表插入都会失败。因此：

insert into ball (color) values ('red');
insert into ball (color) values ('blue');
insert into ball (color) values ('green');

insert into box (color) values ('red');
insert into box (color) values ('blue');
insert into box (color) values ('green');

insert into boxBallRule (ballId,boxId) values ((select id from ball where color = 'red'),(select id from box where color = 'red'));
insert into boxBallRule (ballId,boxId) values ((select id from ball where color = 'blue'),(select id from box where color = 'blue'));
insert into boxBallRule (ballId,boxId) values ((select id from ball where color = 'green'),(select id from box where color = 'green'));

-- Let's try and put a red ball in a green box. 
-- The DB should not allow us to do this!
insert into boxBall (ballId,boxId) values 
((select id from ball where color = 'red'),
 (select id from box where color = 'green'));

最后一条语句应该失败，因为它违反了 boxBallRule 表上的复合外键。

Answer 4

我认为，从关系理论的角度来看，这个问题的答案如下：你在这里真正想说的是，你有一组盒子和一组球，每个球都在一个盒子里。盒子和球都有一种颜色，球只能位于颜色相匹配的盒子中。但将球的颜色存储在球表中是一个设计错误。相反，您应该只存储每个球所在的盒子，然后您知道球是什么颜色，因为您可以检查它存储在其中的盒子的颜色（使用连接）。

所以，不，您没有可以指定的约束来强制执行您想要的关系，但那是因为您以错误的方式处理此问题。 balls 表中不应有 Color 列。

编辑：上面假设每个球都必须在一个盒子里。 OP 澄清并非每个球都需要放在盒子里。这似乎是一个更难的问题，因为在这种情况下，您不能依靠盒子表来跟踪球的颜色。我可以看到一些不同的解决方案，但没有一个是完美的。

1. 遵循您最初的设计，并接受它没有为您提供简单的方法来强制您所考虑的约束。
2. 创建一个新表“unboxed_ball”，用于存储不在盒子中的球，并有一个“颜色”列来记录球的颜色。盒子里的球可以在原来的球表中找到；不在盒子里的球可以在这个新表中找到。要查询所有球，无论是装箱的还是未装箱的，您需要执行 UNION。
3. 将“假盒子”添加到盒子表中，每种颜色各一个，该颜色的未装箱的球被视为“内部”（尽管盒子并不真正存在）。如果盒子还有这个“假盒子”实际上不具备的其他属性，这可能不太实用。