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

我有两张表，如下所述。我的应用程序需要在两个已经存在的记录之间插入tblA中的新记录。例如，如果tblA有6条AID范围从0到5的记录，并且我想插入一条AID为4的新记录，则我将元组4和元组5的AID递增一个，然后插入新记录。因此，我使用以下准备好的语句将AID和tblA元组的列tblB的值（通过级联）增加一个：

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'

现在，我还不清楚到底是什么导致了这个问题，以及如何解决这个问题。
我很感激你的建议。提前谢谢！

最佳答案

关于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号没有意义。除非你知道的比你的问题还多，否则援助和投标都是毫无意义的。在正确设计的数据库中，永远不需要仅基于代理项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   Null
18   Null
19   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;

在update语句中，标准SQL不允许order by在此位置。MySQL documents this作为
如果指定了ORDERBY子句，则按顺序更新行
是指定的。
关系模型和SQL是面向集合的。更新应该“一次完成”。IMHO，您最好学习标准SQL并使用更好地支持标准SQL的dbms。（我突然想到PostgreSQL）但是将on update cascade添加到tblB（上面）将使您的update语句在MySQL中获得成功。

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