如何在Oracle中使用LookUp表? [英] How to use LookUp tables in oracle?

问题描述

在我的数据库中，许多表都有'State'字段，表示该特定实体所处的状态.有人告诉我，对于这种情况，我们应该使用Lookup表，但是我不确定确切的表机制.有人可以澄清这些观点吗?

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?

1. 如何保持完整性? (即如何确保只有状态表中的值才能进入其他表中?)

1. 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?)

状态名称是否进入其他表，或者状态表中的状态ID进入其他表?

Does the state name go into the other tables, or does the state id from the state table go into the other tables?

推荐答案

1-使用所谓的FOREIGN KEY约束维护完整性.一个合理的情况可能是您要做这两个表:

1 - Integrity is maintained using what is called a FOREIGN KEY constraint. A reasonable scenario might have you do these two tables:

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!

这回答了您的问题2:在此示例中，CUSTOMER表中包含状态代码而不是全名.

This answers your question #2: The state codes, not the full names, go in the CUSTOMER table in this example.

将这种结构强加到现有布局上的典型脚本如下:

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);

这将回答您的问题#1:该"REFERENCES"命令将创建一个外键约束，该约束将强制CUSTOMER.STATE_CODE中的所有条目在STATE_CODE表中具有一个对应的条目.设置完之后，如果有人要尝试此操作:

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:

INSERT INTO CUSTOMER(CUST_ID,CUST_NAME,CUST_STATE)
VALUES(9000,'Martians',74837483748);

然后他们将收到一条错误消息，并且永远不会输入错误的数据(当然，除非您确实有一个状态为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).

这篇关于如何在Oracle中使用LookUp表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！