SQL oracle中如何使用LookUp表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6196112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use LookUp tables in oracle?
提问by Devdatta Tengshe
In my database, many tables have the 'State' field, representing the state that, that particular entity falls in. I have been told that we should use Lookup tables for this kind of thing, but I am unsure of the exact mechanism. Can someone clarify these points?
在我的数据库中，许多表都有 'State' 字段，表示该特定实体所属的状态。有人告诉我我们应该对这种事情使用查找表，但我不确定确切的机制。有人可以澄清这些要点吗？
How is the integrity maintained? (i.e. how do I make sure that only the values from the state table go into the other tables?)
Does the state name go into the other tables, or does the state id from the state table go into the other tables?
如何保持完整性？（即如何确保只有状态表中的值才能进入其他表？）
状态名称是否进入其他表，或者状态表中的状态 ID 是否进入其他表？
回答by JosephStyons
1 - Integrity is maintained using what is called a FOREIGN KEY constraint. A reasonable scenario might have you do these two tables:
1 - 使用所谓的 FOREIGN KEY 约束来维护完整性。一个合理的场景可能是你做这两个表：
Table Name: STATE_CODE
ID DESCRIPTION
=================
1 Alabama
2 Arkansas
...
50 Wyoming
Table Name: CUSTOMER
=====================
CUST_ID CUST_NAME CUST_STATE
100 AAA Company 1 --they are in Alabama!
200 ZZZ Company 50 --they are in Wyoming!
This answers your question #2: The state codes, not the full names, go in the CUSTOMER table in this example.
这回答了您的问题 #2：在此示例中，州代码而不是全名进入 CUSTOMER 表。
A typical script to impose this kind of structure on an existing layout would be like this:
将这种结构强加于现有布局的典型脚本如下所示：
--first, create the lookup table
CREATE TABLE STATE_CODE(
ID INTEGER NOT NULL
,DESCRIPTION VARCHAR(100) NOT NULL
,PRIMARY KEY(ID)
);
--now add a reference to the lookup table inside your existing table
--the REFERENCES part will **force** entries
--to have a matching entry in STATE_CODE
ALTER TABLE CUSTOMER ADD STATE_CODE_ID REFERENCES STATE_CODE(ID);
And this answers your question #1: That "REFERENCES" command will create a Foreign Key constraint that will force all entries in CUSTOMER.STATE_CODE to have a corresponding entry in the STATE_CODE table. After setting this up, if someone were to try this:
这回答了您的问题 #1：“REFERENCES”命令将创建一个外键约束，该约束将强制 CUSTOMER.STATE_CODE 中的所有条目在 STATE_CODE 表中具有相应的条目。设置好后，如果有人要尝试这个：
INSERT INTO CUSTOMER(CUST_ID,CUST_NAME,CUST_STATE)
VALUES(9000,'Martians',74837483748);
Then they would get an error message, and that faulty data would never get entered (unless, of course, you really did have a state with a code of 74837483748).
然后他们会收到一条错误消息，并且永远不会输入错误的数据（当然，除非您确实有一个代码为 74837483748 的状态）。
回答by OMG Ponies
Answers:
答案：
Integrity is maintained by foreign key constraints.
A foreign key constraint ensures that the only values the child table will allow in the specified column, come from the parent table's designated column.
For sake of join/various database operations, the smallest data type possible is recommended because the performance will be better.
For example, INT takes 4 Bytes while VARCHAR2(4+) takes more than that. From a performance perspective, it will be faster if you use INT than VARCHAR2(4+). But you do want two columns - one to serve as primary key while the other is the human readable description. This approach allows you to change the description without impacting existing records.
This leads to a discussion about artificial/surrogate and natural keys, for what is best to use as a primary key (and ultimately a foreign key).
完整性由外键约束维护。
外键约束确保子表在指定列中允许的唯一值来自父表的指定列。
为了join/各种数据库操作，建议使用尽可能小的数据类型，因为性能会更好。
例如，INT 需要 4 个字节，而 VARCHAR2(4+) 需要更多。从性能的角度来看，使用 INT 比使用 VARCHAR2(4+) 会更快。但是您确实需要两列 - 一列作为主键，而另一列是人类可读的描述。这种方法允许您在不影响现有记录的情况下更改描述。
这导致了关于人工/代理和自然键的讨论，什么最适合用作主键（最终是外键）。
