SQL：ENUM与一对多关系的优点？ | ENUM与一对多关系的优点

本文介绍了SQL：ENUM与一对多关系的优点？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我很少看到野外使用的ENUM数据类型;一个开发人员几乎总是只使用一个如下的次级表：

I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this:

CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO ranks VALUES (1,'2LT'),(2,'1LT'),(3,'CPT'),(4,'MAJ'),(5,'LTC'),(6,'COL'),(7,'BG'),(8,'MG'),(9,'LTG'),(10,'GEN');

CREATE TABLE officers (
solider_name varchar NOT NULL
,rank int NOT NULL REFERENCES officer_ranks(id) ON DELETE RESTRICT
,serial_num varchar PRIMARY KEY);

但同样的事情也可以使用用户定义的类型/ ENUM来显示：

But the same thing can also be shown using a user-defined type / ENUM:

CREATE TYPE officer_rank AS ENUM ('2LT', '1LT','CPT','MAJ','LTC','COL','BG','MG','LTG','GEN');

CREATE TABLE officers (
solider_name varchar NOT NULL
,rank officer_rank NOT NULL
,serial_num varchar PRIMARY KEY);

（使用PostgreSQL显示的示例，但其他RDBMS具有类似的语法）

(Example shown using PostgreSQL, but other RDBMS's have similar syntax)

我看到使用ENUM最大的缺点是更难于在应用程序内进行更新。而且它也可能会使一个没有经验的开发人员将使用SQL DB的开发人员简单地视为一个小桶。

The biggest disadvantage I see to using an ENUM is that it's more difficult to update from within an application. And it might also confuse an inexperienced developer who's used to using a SQL DB simply as a bit bucket.

假设信息大部分是静态的（工作日名称，月份名称，美国陆军队伍等）使用ENUM有什么优势吗？

Assuming that the information is mostly static (weekday names, month names, US Army ranks, etc) is there any advantage to using a ENUM?

推荐答案

使用类似于ENUM的缺点是如果数据表中不存在，则无法获取所有可用值的列表，除非您在某处硬编码可用值列表。例如，如果在你的OFFICERS表中，你没有发现一个MG没有办法知道排名存在。因此，当BG Blowhard被MG Marjorie-Banks解除后，你将无法进入新军官的排名 - 这是一个耻辱，因为他是现代少将的典范。 :-)当陆军将军（五星级将军）出现时会发生什么？

A disadvantage of using something like an ENUM is that you can't get a list of all the available values if they don't happen to exist in your data table, unless you hard-code the list of available values somewhere. For example, if in your OFFICERS table you don't happen to have an MG on post there's no way to know the rank exists. Thus, when BG Blowhard is relieved by MG Marjorie-Banks you'll have no way to enter the new officer's rank - which is a shame, as he is the very model of a modern Major General. :-) And what happens when a General of the Army (five-star general) shows up?

对于不会改变的简单类型，我已成功使用域名。例如，在我的一个数据库中，我有一个yes_no_domain定义如下：

For simple types which will not change I've used domains successfully. For example, in one of my databases I've got a yes_no_domain defined as follows:

CREATE DOMAIN yes_no_dom
  AS character(1)
  DEFAULT 'N'::bpchar
  NOT NULL
   CONSTRAINT yes_no_dom_check
     CHECK ((VALUE = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])));

分享享受。

这篇关于SQL：ENUM与一对多关系的优点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！