如何在没有外键和主键的两个表之间创建关系?

I received an excel sheet from a client. It has three lines which I cannot understand.

我从客户那里收到了一张excel表格。它有三条我无法理解的线。

In test table's row 4 (line 6) "RelationTo" column's testCategoryList.CategoryId. Does it mean that test table's foreign key testCategory is the Primary key of testCategoryList table?

在测试表的第4行(第6行)“RelationTo”专栏的testCategoryList.CategoryId。是否意味着测试表的外键testCategory是testCategoryList表的主键?

But, test table's foreign key testCategory is not the primary key of testCategoryList table. Other two rows (line 7 and line 8) have the same structure.

但是，test table的外键testCategory不是testCategoryList表的主键。其他两行(第7行和第8行)具有相同的结构。

testCategoryList table:

testCategoryList表:

testSubjectList table:

testSubjectList表:

testLevelList table:

testLevelList表:

Am I missing something? Those three lines don't make any sense.

我遗漏了什么东西?这三行没有任何意义。

1 个解决方案

#1

I think your confusion might be over naming conventions.

我想你的困惑可能是关于命名约定。

Are you assuming that a foreign key column name must be identical to a primary key column name in order to match them? This is absolutely not true. There was a time in pre-relational database days where some storage technologies did have this requirement, but it is not part of the SQL standard at all.

您是否假设一个外键列名必须与主键列名相同，以便与它们匹配?这绝对不是真的。在前关系数据库时代有一段时间，一些存储技术确实有这种需求，但它根本不是SQL标准的一部分。

Nevertheless, there is a naming convention which is popular with many people in which foreign key names match primary key names exactly. This enables something called natural joins.

尽管如此，还是有一个命名约定，它在许多人当中很受欢迎，在其中，外键名与主键名完全匹配。这就使所谓的自然连接成为可能。

However, not everyone agrees that this naming convention is a good idea. A couple of problems with it are that (a) if you have two FK's from one table to another, you have to let go of the convention to avoid duplicate column names, and (b) you end up having to put the table name in front of every single column to avoid unintended natural joins, such as person.name against company.name.

然而，并不是所有人都同意这种命名约定是个好主意。几个问题是(A)如果你有两颗的从一个到另一个表,你必须放开公约,以避免重复的列名,和(b)你最终不得不把每一列的表名在前面,避免意想不到的自然连接,如对company.name person.name。

What your spreadsheet is clearly showing is that the FKs on the test table point to the PKs of the other three tables. For example the CREATE TABLE script for the test table would include:

你的电子表格清楚显示的是，测试表上的FKs指向另外三个表的PKs。例如，测试表的CREATE TABLE脚本包括:

...
CONSTRAINT FK_TEST__TESTCATEGORYLIST FOREIGN KEY
    IX_TEST__TESTCATEGORY (testCategory)
    REFERENCES testCategoryList (categoryId)
...

#1