oracle 数据库设计 - 是否应将日期用作主键的一部分

Database design - Should a Date be used as part of a primary key

提问by rich

What are the pros/cons for including a date field as a part of a primary key?

将日期字段作为主键的一部分包含哪些优点/缺点？

回答by David Aldridge

Consider a table of parts inventory -- if you want to store the inventory level at the end of each day then a composite primary key on part_id and date_of_day would be fine. You might choose to make that a unique key and add a synthetic primary key, particularly if you have one or more tables referencing it with a foreign key constraint, but that aside, no problem.

考虑一个零件库存表——如果您想在每天结束时存储库存水平，那么 part_id 和 date_of_day 上的复合主键就可以了。您可能会选择使其成为唯一键并添加一个合成主键，特别是如果您有一个或多个表使用外键约束引用它，但除此之外，没问题。

So there's nothing necessarily wrong with it, but like any other method it can be used incorrectly as in Patrick's example.

所以它没有什么问题，但像任何其他方法一样，它可能会像帕特里克的例子那样被错误地使用。

Edit: Here's another comment to add.

编辑：这是要添加的另一条评论。

I'm reminded of something I wrote a while ago on the subject of whether date values in databases really were natural or synthetic. The readable representation of a date as "YYYY-MM-DD" is certainly natural, but internally in Oracle this is stored as a numeric that just represents that particular date/time to Oracle. We can choose and change the representation of that internal value at any time (to different readable formats, or to a different calendar system entirely) without the internal value losing its meaning as thatparticular date and time. I think on that basis, a DATE data type lies somewhere between natural and synthetic.

我想起了我不久前写的关于数据库中的日期值究竟是自然的还是合成的主题。日期的可读表示为“YYYY-MM-DD”当然是自然的，但在 Oracle 内部，这存储为一个数字，仅代表 Oracle 的特定日期/时间。我们可以选择并随时更改该内部值的表示（以不同的可读格式，或完全不同的日历系统）而无需内部值失去其意义在于特定的日期和时间。我认为在此基础上，DATE 数据类型介于自然和合成之间。

回答by Patrick Harrington

I am ok with it being part of the key, but would add that you should also have an auto-incrementing sequence number be a part of the PK, and enforce that any date is written to the database as UTC, with the downstream systems than converting to local time.

我认为它是密钥的一部分，但我想补充一点，您还应该有一个自动递增的序列号作为 PK 的一部分，并强制将任何日期作为 UTC 写入数据库，下游系统比转换为当地时间。

A system that I worked in decided that it would be a grand idea to have an Oracle trigger write to a database whenever another table was touched, and make the sysdate be part of the primary key with no sequence number. Only problem is that if you run an update query that hits the row more than once per second, it breaks the primary key on the table that is recording the change.

我工作的一个系统认为，每当另一个表被触及时，让 Oracle 触发器写入数据库，并使 sysdate 成为没有序列号的主键的一部分，这将是一个伟大的想法。唯一的问题是，如果您运行的更新查询每秒多次命中该行，它会破坏记录更改的表上的主键。

回答by Steven A. Lowe

if you have already decided to use a 'natural' primary key, then question is: is the date a necessary part of the primary key, or not - pros/cons are irrelevant!

如果您已经决定使用“自然”主键，那么问题是：日期是主键的必要部分，还是不是 - 利弊无关紧要！

回答by Thomas Jones-Low

There are some questions I'd ask about using a date as part of the primary key.

关于使用日期作为主键的一部分，我会问一些问题。

Does the date include the time portion? This makes things tricky because time includes time zones and daylight savings. This doesn't alter the date/time value, but may produce unexpected results in terms of sorting or retrieving values based upon a query.

日期是否包括时间部分？这让事情变得棘手，因为时间包括时区和夏令时。这不会改变日期/时间值，但可能会在基于查询的排序或检索值方面产生意外结果。

I'm a big believer in the use of surrogate keys (i.e. use a sequence column as the primary key) rather than natural keys (like using a date).

我非常相信使用代理键（即使用序列列作为主键）而不是自然键（如使用日期）。

回答by RobS

As always.. It depends.

一如既往..这取决于。

What is your objective of including a date/time column in a PK? Is it to provide additional information about a record without having to actually select the row?

您在 PK 中包含日期/时间列的目的是什么？是否提供有关记录的附加信息而不必实际选择行？

The main problem I can foresee here is the obvious ones, i.e. do you use a UTC date or a local date? Will the date be misinterpreted (will someone think it means local time when it means UTC)? As some of the others have suggested this might be better used in a surrogate/composite key instead? It might be better for your performance to use it in a key or index other than the Primary Key.

我在这里可以预见的主要问题是显而易见的问题，即您使用的是 UTC 日期还是本地日期？日期是否会被误解（有人会认为它是指当地时间，而指的是 UTC）吗？正如其他一些人所建议的那样，这可能更好地用于代理/复合键？在主键以外的键或索引中使用它可能对您的性能更好。

[Side note] This kind of reminds me of the theory behind a (1) COMB(combined GUID) although the idea here was to create a unique ID for a PK which SQL Server better indexed/required less index rebuilding, rather than to add any meaningful date/time value to a row.

[旁注] 这种让我想起(1) COMB（组合 GUID）背后的理论，尽管这里的想法是为 SQL Server 更好地索引/需要更少索引重建的 PK 创建唯一 ID，而不是添加任何有意义的日期/时间值到一行。

(1) [http://www.informit.com/articles/article.aspx?p=25862&seqNum=7]

(1) [ http://www.informit.com/articles/article.aspx?p=25862&seqNum=7]

回答by cagcowboy

A slight con would be that it's not as elegant a handle as some other identifiers

一个轻微的缺点是它不像其他一些标识符那样优雅

(e.g. saying to a colleague please can you look at record 475663 is a bit easier than saying please can you look at 2008-12-04 19:34:02)

（例如对同事说请你看记录475663比说请你看2008-12-04 19:34:02容易一点）

There is also the risk of confusion over different date format in different locales

不同地区的不同日期格式也存在混淆的风险

(e.g. 4th March 2008 - 4/3/2008 in Europe, 3/4/2008 in USA)

（例如 2008 年 3 月 4 日 - 欧洲 4/3/2008，美国 3/4/2008）

(My preference is always to use a seperate key column)

（我的偏好总是使用单独的键列）

回答by Tony Andrews

Dates make perfectly good primary keys, provided that they make sense as part of the natural key. I would use a date in tables like:

日期是非常好的主键，前提是它们作为自然键的一部分有意义。我会在表中使用日期，例如：

• holiday_dates (hol_date date)
• employee_salary (employee_id integer, sal_start_date date)

• Holiday_dates（hol_date 日期）
• employee_salary（employee_id 整数，sal_start_date 日期）

(What would be the point of adding the surrogate employee_salary_id above?)

（在上面添加代理employee_salary_id 有什么意义？）

For some tables, a date could be used but something else makes more sense as the primary key, e.g.:

对于某些表，可以使用日期，但其他东西作为主键更有意义，例如：

• hotel_room_booking (booking_reference)

• hotel_room_booking (booking_reference)

We could have used (room_no, booking_from_date) or (room_no, booking_to_date), but a reference is more useful for communicating with the client etc. We might makes these into UNIQUE constraints, but in fact we need a more complex "no overlap" check for these.

我们可以使用 (room_no,booking_from_date) 或 (room_no,booking_to_date)，但引用对于与客户端等通信更有用。我们可能会将它们设为 UNIQUE 约束，但实际上我们需要更复杂的“无重叠”检查对于这些。

回答by Witold Kaczurba

It might be hard to refer to. I came across _ID + _Date as a composite PK. This composite key was also a reference/FK in another table.

可能很难参考。我遇到了 _ID + _Date 作为复合 PK。这个组合键也是另一个表中的引用/FK。

Firstly it was purely confusing as there was _ID that suggested a non-composite key.

首先，这纯粹是令人困惑的，因为有 _ID 建议使用非复合密钥。

Secondly Inserts to the main table were done with SYSDATE and one needed to figure out precise time that was in that SYSDATE. You need to be precise about time that is in it when you refer to it.Otherwise it will not work...

其次，对主表的插入是使用 SYSDATE 完成的，需要计算出该 SYSDATE 中的精确时间。当你提到它时，你需要精确地说明它的时间。否则将无法正常工作...

回答by David Waters

Date as the sole or first component of a primary key causes performance problems on tables with high insert. (Table will need to be rebalanced frequently).

日期作为主键的唯一或第一个组件会导致高插入表的性能问题。（表需要经常重新平衡）。

Often causes an issue if more then one are inserted per Date.

如果每个日期插入多个，通常会导致问题。

In most situations I consider this a bad smell, and would advise against it.

在大多数情况下，我认为这是一种难闻的气味，并建议不要这样做。

回答by James Anderson

Nothing particulary wrong with this but as other posters have noted you could get into problems with time zones and locals. Also you can end up with lots of DATE() functions obfusticating your SQL.

这没有什么特别的错误，但正如其他海报所指出的那样，您可能会遇到时区和当地人的问题。此外，您最终可能会使用大量 DATE() 函数来混淆您的 SQL。

If it is something like inventory at end of day as previously mentioned, you could perhaps consider an eight character text field like "20081202" as the second part of the primary key. This avoids the time zone locale problems and is easy enough to convert into a real date if you need to.

如果它类似于前面提到的一天结束时的库存，您也许可以考虑将一个八字符的文本字段（如“20081202”）作为主键的第二部分。这避免了时区区域设置问题，并且很容易在需要时转换为真实日期。

Remember the primary key has two functions to uniquly identify a record and to enforce uniqueness. Surrogate primary keys do niether.

请记住，主键有两个功能来唯一地标识记录和强制唯一性。代理主键也没有。