mysql - 更新主键会导致外表重复条目

Question

我有两个表由以下 SQL Fiddle 描述.我的应用程序需要在 tblA 中的两个现有记录之间插入新记录。例如，如果 tblA 有 6 条记录，AID 的范围从 0 到 5，而我想插入一条 AID 为 4 的新记录，我递增 AID 将元组 4 和元组 5 逐一插入，然后插入新记录。因此，我使用以下准备好的语句将 tblA 和 tblB 的元组的列 AID 的值递增(通过级联)一:

update tblA set AID = (AID + 1) where AID >= ? order by AID desc;

在我的测试安装中，上述声明效果很好。然而，在我们的生产系统上，我们在某些情况下会收到以下错误消息，但并非所有情况:

Foreign key constraint for table 'tblA', record '4' would lead to a duplicate entry in table 'tblB'

现在，我不清楚究竟是什么导致了这个问题以及如何解决这个问题。

我很感激任何提示。提前致谢!

Answer 1

关于 tblB

这个

create table if not exists tblB(
  BID integer not null, 
  AID integer not null, 
  constraint fkB_A foreign key(AID) references tblA(AID), 
  primary key(AID, BID)
);

应该是

create table if not exists tblB(
  BID integer not null, 
  AID integer not null, 
  constraint fkB_A foreign key(AID) references tblA(AID)
    on update cascade, 
  -- ^^^^^^^^^^^^^^^^
  primary key(AID, BID)
);

数据关系模型和 SQL 数据库中的代理 ID 号没有意义。除非你知道的比你在问题中包含的更多，否则 AID 和 BID 是没有意义的。在设计得当的数据库中，永远不需要另外两行仅根据它们的代理 ID 号插入一行。

如果您的实际需求只是在“2015-12-01 23:07:00”和“2015-12-04 14:58:00”之间插入时间戳，则不需要 ID 号4 做到这一点。

-- Use single quotes around timestamps.
insert into tblA values (-42, '2015-12-03 00:00:00');
select * from tblA order by RecordDate;

AID       RecordDate--  0       2015-11-07 16:55:00  1       2015-11-08 22:16:00  2       2015-11-10 14:26:00  3       2015-12-01 23:07:00-42       2015-12-03 00:00:00  5       2015-12-04 14:58:00  6       2015-12-13 10:07:00

About tblA

This

create table if not exists tblA(
  AID integer not null, 
  RecordDate varchar(25), 
  constraint pkA primary key(AID)
);

应该是

create table if not exists tblA(
  AID integer not null, 
  RecordDate varchar(25) not null, 
  --                     ^^^^^^^^
  constraint pkA primary key(AID)
);

没有那个not null，你可以像这样插入数据。

AID  RecordDate--17   Null18   Null19   Null

Since surrogate ID numbers are meaningless, these rows are all essentially both identical and identically useless.

About the update statement

update tblA 
set AID = (AID + 1) 
where AID >= 4 
order by AID desc;

标准 SQL 不允许在更新语句的这个位置使用 order by。 MySQL documents this作为

If the ORDER BY clause is specified, the rows are updated in the order that is specified.

关系模型和 SQL 都是面向集合的。更新应该“一次全部”发生。恕我直言，你最好学习标准 SQL 并使用更好地支持标准 SQL 的 dbms。 (我想到了 PostgreSQL。)但是将 on update cascade 添加到 tblB(上文)将使您的更新语句在 MySQL 中成功。

update tblA 
set AID = (AID + 1) 
where AID >= 4 order by AID desc;