使用时间戳作为主键时，避免主键约束违规

I have a database table where the primary key is defined as a timestamp. The application inserts records to the database based on a certain event that is triggered and the timestamp used will be the one that created this instance. Since this application is running on multiple servers and that event is triggering at the same time in more than one of these servers at the same time, in some cases, the data doesn't get inserted to the database due to the primary key violation.

我有一个数据库表，其中主键被定义为时间戳。应用程序根据触发的特定事件将记录插入数据库，并且使用的时间戳将是创建此实例的时间戳。由于此应用程序在多个服务器上运行，并且该事件同时在多个服务器中同时触发，因此在某些情况下，由于主键冲突，数据不会插入到数据库中。

What could I possible change in order to avoid this situation? The ideas that I have at the moment are,

为了避免这种情况，我可以改变什么？我现在的想法是，

Waiting a random time before executing the database insertion.

在执行数据库插入之前等待一段随机时间。

Random r = new Random();
long time = r.nextLong() % 1000;
time = time < 0 ? -time : time; // making sure that the waiting time is positive
try {
    Thread.sleep(time);
} catch (InterruptedException e) {
    throw new CustomeException(e);
}
Table1DataAccess.getInstance().insert(new Date(), "some description"); // inserting data into the table which has two fields.

Making the primary key a composite one.

使主键成为复合键。

Neither of them seems like good solutions to me, could anyone point me in the right direction?

对我来说，它们似乎都不是很好的解决方案，有人能指出我正确的方向吗？

P.S. The table to which I tried inserting data had the timestamp column in Date type and I guess it only has it's precision down to seconds. When I used the type timestamp instead, it offered a default precision of microseconds and it can offer nanoseconds at maximum.

附：我尝试插入数据的表在Date类型中有timestamp列，我猜它只有几秒的精度。当我使用类型时间戳时，它提供了默认的微秒精度，它最多可以提供纳秒。

4 个解决方案

#1

Do not use timestamp as primary key field.

不要将时间戳用作主键字段。

Use Integer or BigInt for primary key auto increment fields

使用Integer或BigInt作为主键自动增量字段

#2

Waiting for a random time to insert may not work if you have a large number of inserts. The answer assumes that you want to keep timestamp and make minimal changes in your code and do not want to go to auto increment features available in DB.

如果您有大量插入，则等待随机插入时间可能无效。答案假定您希望保留时间戳并对代码进行最小的更改，并且不希望转到DB中可用的自动增量功能。

I have seen a similar problem long back and we modified the table to include originating server node to address it. So, either you expand the timestamp column and save timestamp_server or you add an extra column , server_node and then work with it. It will depend upon how you fetch the data. Adding server_node column will be less intrusive. However, do you need to update the data as well? Primary keys cannot be updated. So that will need to be considered in design considerations.

我早就看到了类似的问题，我们修改了表以包含发起服务器节点来解决它。因此，要么展开timestamp列并保存timestamp_server，要么添加一个额外的列server_node，然后使用它。这取决于您获取数据的方式。添加server_node列将不那么具有侵入性。但是，您是否还需要更新数据？主键无法更新。因此，在设计考虑因素中需要考虑这一点。

#3

Define the primary key as an autoincremet integer/long field

将主键定义为autoincremet整数/长字段

#4

First of all, it is not wise to use the timestamp as a primary key. The primary key should be something that uniquely identifies your database entry. And that is not the case in your application: even in the same server, you could get a conflict.That is one of the reasons why all major database engines provide a mechanism for automatically generating the primary key. Also check this answer for more details.

首先，使用时间戳作为主键是不明智的。主键应该是唯一标识数据库条目的东西。在您的应用程序中情况并非如此：即使在同一服务器中，您也可能会发生冲突。这就是为什么所有主要数据库引擎都提供自动生成主键的机制的原因之一。另请查看此答案以获取更多详细信息

In the hypothetical case of using the timestamp (maybe because it is too late to change your overall design now), you could prepend a unique identifier of the respective server to your timestamp. Nevertheless, this will not be a unique identifier in the case of same-time writing within the same server. Then, you will need to append another incremental identifier when catching the error of primary key violation. And this will not entirely solve your problem. You could -for instance- have the same incremental identifier within the same server. You see how messy this process can get.

在使用时间戳的假设情况下（可能因为现在改变整体设计为时已晚），您可以在相应的时间戳前面添加相应服务器的唯一标识符。然而，在同一服务器内同时写入的情况下，这不是唯一标识符。然后，在捕获主键冲突错误时，您需要附加另一个增量标识符。这并不能完全解决你的问题。例如，您可以在同一服务器中拥有相同的增量标识符。你看这个过程有多乱。

So, the take-home message is that it would save you a lot of time and pain if you allowed the DB engine to assign the primary key for you.

因此，如果您允许数据库引擎为您分配主键，那么带回家的消息是它会为您节省大量时间和精力。

#1