使用主键的一部分作为外键

Question

我正在构建一个小的MYSQL数据库，目前我的整个家遇到了一个问题。它的属性是ROOM_NUM和BUILD_CODE。这两个键组成一个复合主键，外键BUILD_CODE引用它所在的建筑。

我想知道是否可以将BUILD_CODE作为单个字符('a‘、'b’、'c‘等)。然后将HOME的主键设置为类似于"A302“或"B205”的内容。主键的第一个字符将引用BUILD_CODE，其余的数字是房间号。这样我就有了一个唯一的单值主键。我这样问的原因是，这样我就不必在PERSON表中有多个引用HOME的外键。

Answer 1

您可以使用代理键(序列)作为实体HOME的主键，并将其用作PERSON的外键。然后为BUILD_CODE和ROOM_NUM声明一个唯一的键，以保持组合的唯一性。

Answer 2

听起来HOME需要自己的单独标识符。ID is...as是什么并不重要，只要它是唯一的。

请考虑以下几点：

mysql> CREATE TABLE HOME (ID INTEGER NOT NULL auto_increment, ROOM_NUM INTEGER, BUILD_CODE VARCHAR(1), PRIMARY KEY(ID));
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE PERSON(ID INTEGER NOT NULL auto_increment, NAME VARCHAR(255), HOME_ID INTEGER, PRIMARY KEY(ID), FOREIGN KEY(HOME_ID) REFERENCES HOME(ID));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO HOME (ROOM_NUM, BUILD_CODE) VALUES (302, "A"), (205, "B");
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM HOME;
+----+----------+------------+
| ID | ROOM_NUM | BUILD_CODE |
+----+----------+------------+
|  1 |      302 | A          |
|  2 |      205 | B          |
+----+----------+------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO PERSON (NAME, HOME_ID) VALUES ("someone", 1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM PERSON;
+----+---------+---------+
| ID | NAME    | HOME_ID |
+----+---------+---------+
|  1 | someone |       1 |
+----+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT PERSON.NAME, HOME.BUILD_CODE, HOME.ROOM_NUM FROM HOME JOIN PERSON ON PERSON.HOME_ID = HOME.ID;
+---------+------------+----------+
| NAME    | BUILD_CODE | ROOM_NUM |
+---------+------------+----------+
| someone | A          |      302 |
+---------+------------+----------+
1 row in set (0.00 sec)

您看，HOME的ID值并不代表其其余数据，这真的无关紧要……您只是将该字段用作将两者连接在一起的工具。

当然，您也可以很容易地将BUILD_CODE和ROOM_NUM字段连接到ID字段中...那也很好。但请记住，(a)字符串查找比整数查找使用更多的资源，(b)这只是您的应用程序需要维护的另一件事(例如:如果一开始将房间号计算为A302，但在看到更大的房间号后，您决定使用A00302，会发生什么情况？您必须遍历整个表--及其所有关联--并更新ID。它不是非常理想)。