如何在Oracle中使用查找表？

Question

在我的数据库中，许多表都有“State”字段，表示该特定实体所属的州。我被告知应该使用查找表来处理这种情况，但我不确定确切的机制。能否有人澄清以下几点？

1. 如何维护完整性？（即如何确保只有状态表中的值进入其他表中？） 2. 状态名称是否进入其他表中，还是状态表中的状态ID进入其他表中？

- Devdatta Tengshe

Answer 1

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!

这是回答问题#2的答案：在这个例子中，状态代码而不是全名放在CUSTOMER表中。

对于现有布局实施这种结构的典型脚本如下：

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

这里回答了您的第一个问题：使用"REFERENCES"命令将创建一个外键约束，强制CUSTOMER.STATE_CODE中的所有条目都在STATE_CODE表中有相应的条目。设置完成后，如果有人尝试执行以下操作：

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

然后他们会收到一个错误消息，这个错误数据将永远不会被输入（除非你真的有一个状态码为74837483748的州）。

- JosephStyons

---

非常详细的解释！谢谢你！ - Devdatta Tengshe

Answer 2

答案：

1. 通过外键约束维护数据完整性。

   外键约束确保子表中指定列允许的唯一值来自于父表的指定列。

2. 为了进行连接/各种数据库操作，建议使用尽可能小的数据类型，因为性能会更好。

   例如，INT占用4个字节，而VARCHAR2（4+）占用的字节更多。从性能角度来看，如果使用INT而不是VARCHAR2（4+），速度会更快。但您确实需要两列——一列用作主键，另一列是可读性描述。这种方法使您可以更改描述而不影响现有记录。

   这引出了人工/替代以及自然键的讨论，关于何时最好使用主键（以及最终的外键）。

- OMG Ponies