当前位置：文江博客话题详情

定义“ENUM”的最佳方式是什么？在Oracle 10中创建表时的数据类型？

发布于 2024-09-24 05:47:00 字数 65 浏览 1 评论 0原文

例如：我想要一个名为 season 的数据类型 (= { spring, Summer, Fall, Winter}

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

饭团 2024-10-01 05:47:00

定义一个season表，有两列：season_id和season_name：

创建表“季节”(
   “SEASON_ID”NUMBER NOT NULL 启用， 
   “SEASON_NAME”VARCHAR2(6 BYTE) NOT NULL 启用， 
   约束“SEASON_PK”主键（“SEASON_ID”）
）

添加一个season_id 列到记录需要季节标识的表，并创建与 season 表的外键关系

CHECK 约束是下一个选择，但如果您想使用多个表中的值，则很糟糕，因为它们无法集中管理。触发器也是一种选择，但在这种情况下，与 CHECK 约束相比，触发器更像是杀死蚊子的大炮……

哪个最好？

引用 sql_mommy 的话：

[单独的表，具有外键关系]是最好的。
它是管理所需类型的单一位置，并且非常灵活，因此您可以根据需要添加/减去值或进行编辑。它也有助于良好的索引。如果您可能会想“但是这个枚举的内容永远不需要编辑” - 根据我的经验，所有数据类型最终都会变形。例如，如果您有月份列表，稍后您可能会添加月份的各种缩写或与这些月份的业务相关的关联。使用数据库的关系结构提供了最大的灵活性和最简单的可维护性。

Define a season table, having two columns: season_id, and season_name:

CREATE TABLE "SEASON" (
   "SEASON_ID" NUMBER NOT NULL ENABLE, 
   "SEASON_NAME" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
   CONSTRAINT "SEASON_PK" PRIMARY KEY ("SEASON_ID")
)

Add a season_id column to table(s) whose records need season identification, and create a foreign key relationship to the season table

CHECK constraints are the next option, but suck if you want to use the values in more than one table because they can't be centrally managed. Triggers are also an option, but in this case triggers are more like a cannon to kill a mosquito compared to a CHECK constraint...

Which is best?

To quote sql_mommy:

The [separate table, with a foreign key relationship,] is the best.
It is a single place for managing the desired type, and it's flexible so you can add/subtract values or edit as needed. It makes for good indexing, too. And in case you might think "but the contents of this enum will never need to be edited" - in my experience ALL data types eventually morph. For example, if you have a list of months, later you might add various abbreviations of months or business related associations with those months. Using the relational structure of the database provides the most flexibility with the easiest maintainability.

回复收藏 0 原文

~没有更多了~