SQL:创建具有 2 个不同 auto_increment 的关系表

SQL: Creating a relation table with 2 different auto_increment(SQL:创建具有 2 个不同 auto_increment 的关系表)

问题描述

我有 2 个表，它们都有自己的自动递增 ID，它们当然是主键.

I have 2 tables, both with their own auto incremented IDs, which are of course primary keys.

当我想创建第三个表来建立这两个表之间的关系时，总是出错.

When I want to create a 3rd table to establish the relation between these 2 tables, I always have an error.

第一个是关于你只能有 1 个自动递增的列，当我从那 2 个中删除 auto_increment 语句时发生第二个，因此 sql 不允许我将它们设为外键，因为类型匹配失败.

First one is about that you can have only 1 automatically-incremented column, the second one occurs when I delete the auto_increment statement from those 2, therefore sql doesn't allow me to make them foreign keys, because of the type matching failure.

有没有一种方法可以在不丢失自动增量功能的情况下创建关系表?

Is there a way that I can create a relational table without losing auto increment features?

另一种可能(但不是首选)的解决方案可能是第一个表中还有另一个主键，它是用户的用户名，当然不是使用自动增量语句.不可避免吗?

Another possible (but not preffered) solution may be there is another primary key in the first table, which is the username of the user, not with an auto increment statement, of course. Is it inevitable?

提前致谢.

推荐答案

概念

您误解了一些基本概念，由此产生了困难.我们必须首先解决概念，而不是您所感知的问题，因此，您的问题就会消失.

Concepts

You have misunderstood some basic concepts, and the difficulties result from that. We have to address the concepts first, not the problem as you perceive it, and consequently, your problem will disappear.

自动递增的 ID，当然是主键.

不，他们不是.这是一个普遍的误解.问题肯定会接踵而至.

No, they are not. That is a common misconception. And problems are guaranteed to ensue.

ID 字段不能是英语、技术或关系意义上的主键.

An ID field cannot be a Primary Key in the English or technical or Relational senses.

• 当然，在 SQL 中，您可以将 任何 字段声明为 PRIMARY KEY，但这并不会神奇地将其转换为主键英语、技术或关系意义.您可以将吉娃娃命名为罗威纳犬"，但这并不能将其转变为罗威纳犬，它仍然是吉娃娃.像任何语言一样，SQL 只是执行你给它的命令，它不理解 PRIMARY KEY 意味着什么关系，它只是在列(或字段)上敲击一个唯一的索引.

• Sure, in SQL, you can declare any field to be a PRIMARY KEY, but that doesn't magically transform it into a Primary Key in the English, technical, or Relational senses. You can name a chihuahua "Rottweiller", but that doesn't transform it into a Rottweiller, it remains a chihuahua. Like any language, SQL simply executes the commands that you give it, it does not understand PRIMARY KEY to mean something Relational, it just whacks an unique index on the column (or field).

问题是，既然你已经声明ID是一个PRIMARY KEY，你认为 将其作为主键，您可能期望它具有主键的某些特性.除了 ID value 的唯一性之外，它没有任何好处.它没有主键的任何特性，也没有任何类型的关系键.它不是英语、技术或关系意义上的 Key.将一个非键声明为键，只会让你自己感到困惑，只有当用户抱怨表中有重复项时，你才会发现有什么可怕的错误.

The problem is, since you have declared the ID to be a PRIMARY KEY, you think of it as a Primary Key, and you may expect that it has some of qualities of a Primary Key. Except for the uniqueness of the ID value, it provides no benefit. It has none of the qualities of a Primary Key, or any sort of Relational Key for that matter. It is not a Key in the English, technical, or Relational senses. By declaring a non-key to be a key, you will only confuse yourself, and you will find out that there is something terribly wrong only when the user complains about duplicates in the table.

ID 字段上的 PRIMARY KEY 不提供 行 唯一性.因此，它不是包含行的关系表，如果不是，则它是包含记录的文件.它没有关系数据库中的表所具有的任何完整性或能力(在此阶段您将只知道连接能力)或速度.

A PRIMARY KEY on an ID field does not provide row uniqueness. Therefore it is not a Relational table containing rows, and if it isn't that, then it is a file containing records. It doesn't have any of the integrity, or power (at this stage you will be aware of join power only), or speed, that a table in a Relational database has.

执行此代码 (MS SQL 2008) 和向自己证明.请不要简单地阅读并理解它，然后继续阅读本答案的其余部分，在进一步阅读之前必须执行此代码.具有治疗价值.

    CREATE TABLE dumb_file (
        id         INT      NOT NULL  IDENTITY  PRIMARY KEY,
        name_first CHAR(30) NOT NULL,
        name_last  CHAR(30) NOT NULL
        )

    INSERT dumb_file VALUES ( "Mickey", "Mouse" )  -- succeeds
    INSERT dumb_file VALUES ( "Mickey", "Mouse" )  -- succeeds, but not intended
    INSERT dumb_file VALUES ( "Mickey", "Mouse" )  -- succeeds, but not intended

    SELECT * FROM dumb_file

请注意，您有重复的行.关系表必须具有唯一的行.进一步证明您没有关系表，也没有关系表的任何特性.

Notice that you have duplicate rows. Relational tables are required to have unique rows. Further proof that you do not have a relational table, or any of the qualities of one.

请注意，在您的报告中，唯一唯一的是 ID 字段，没有用户关心，没有用户看到，因为它不是数据，所以一些额外的废话非常愚蠢的老师"告诉你把每个文件都放进去.您具有记录唯一性，但没有行唯一性.

Notice that in your report, the only thing that is unique is the ID field, which no user cares about, no user sees, because it is not data, it is some additional nonsense that some very stupid "teacher" told you to put in every file. You have record uniqueness but not row uniqueness.

就数据而言(真实数据减去多余的添加)，数据name_last和name_first可以在没有ID字段的情况下存在.一个人有名字和姓氏，但没有在额头上盖上身份证.

In terms of the data (the real data minus the extraneous additions), the data name_last and name_first can exist without the ID field. A person has a first name and last name without an ID being stamped on their forehead.

您使用的第二个让您感到困惑的东西是 AUTOINCREMENT. 如果您正在实施一个没有关系功能的记录归档系统，当然，这很有帮助，您不必编码插入记录时的增量.但是如果你正在实现一个关系数据库，它根本没有任何用处，因为你永远不会使用它.SQL 中有许多大多数人从未使用过的功能.

The second thing that you are using that confuses you is the AUTOINCREMENT. If you are implementing a record filing system with no Relational capability, sure, it is helpful, you don't have to code the increment when inserting records. But if you are implementing a Relational Database, it serves no purpose at all, because you will never use it. There are many features in SQL that most people never use.

那么，您如何将充满重复行的哑文件升级、提升到关系表，以获得关系表的一些品质和好处?这需要三个步骤.

So how do you upgrade, elevate, that dumb_file that is full of duplicate rows to a Relational table, in order to get some of the qualities and benefits of a Relational table ? There are three steps to this.

1. 你需要了解 Keys

1. You need to understand Keys

• 由于我们已经从 1970 年代的 ISAM 文件发展到关系模型，您需要了解关系键.也就是说，如果您希望获得关系数据库的好处(完整性、功能、速度).

• And since we have progressed from ISAM files of the 1970's, to the Relational Model, you need to understand Relational Keys. That is, if you wish to obtain the benefits (integrity, power, speed) of a Relational Database.

EF Coo​​d 博士在他的 RM 中宣称:

Dr E F Cood, in his RM, declared that:

密钥由数据组成

和

表中的行必须是唯一的

您的密钥"不是由数据组成的.它是一些额外的，非数据寄生虫，由您感染了老师"的疾病引起.认识到这一点，并让自己拥有上帝赋予你的全部智力(请注意，我不会要求你以孤立的、碎片化的或抽象的方式思考，数据库中的所有元素都必须相互集成).从数据中构成一个真正的关键，并且仅从数据中.在这种情况下，只有一个可能的 Key:(name_last, name_first).

Your "key" is not made up from the data. It is some additional, non-data parasite, caused by your being infected with the disease of your "teacher". Recognise it as such, and allow yourself the full mental capacity that God gave you (notice that I do not ask you to think in isolated or fragmented or abstract terms, all the elements in a database must be integrated with each other). Make up a real key from the data, and only from the data. In this case, there is only one possible Key: (name_last, name_first).

试试这个代码，声明对数据的唯一约束:

     CREATE TABLE dumb_table (
        id         INT      NOT NULL  IDENTITY  PRIMARY KEY,
        name_first CHAR(30) NOT NULL,
        name_last  CHAR(30) NOT NULL

        CONSTRAINT UK 
            UNIQUE ( name_last, name_first )
        )

    INSERT dumb_table VALUES ( "Mickey", "Mouse" )  -- succeeds
    INSERT dumb_table VALUES ( "Mickey", "Mouse" )  -- fails, as intended
    INSERT dumb_table VALUES ( "Minnie", "Mouse" )  -- succeeds

    SELECT * FROM dumb_table

现在我们有了行唯一性.大多数人都会遇到这种情况:他们创建一个允许欺骗的文件；他们不知道为什么会出现在下拉菜单中；用户尖叫；他们调整文件并添加索引以防止被骗；他们去下一个错误修复.(他们可能正确与否，那是另一回事.)

Now we have row uniqueness. That is the sequence that happens to most people: they create a file which allows dupes; they have no idea why dupes are appearing in the drop-downs; the user screams; they tweak the file and add an index to prevent dupes; they go to the next bug fix. (They may do so correctly or not, that is a different story.)

第二级.对于那些想超越固定方法的思考者.既然我们现在有了行唯一性，那么ID 字段的目的是什么，我们为什么还要有它???哦，因为吉娃娃叫 Rotty，我们不敢碰它.

The second level. For thinking people who think beyond the fix-its. Since we have now row uniqueness, what in Heaven's name is the purpose of the ID field, why do we even have it ??? Oh, because the chihuahua is named Rotty and we are afraid to touch it.

声明它是一个 PRIMARY KEY 是错误的，但它仍然存在，导致混淆和错误的期望.唯一真正的密钥是 (name_last, name_fist), 并且它是一个 备用密钥.

The declaration that it is a PRIMARY KEY is false, but it remains, causing confusion and false expectations. The only genuine Key there is, is the (name_last, name_fist), and it is a Alternate Key at this point.

因此 ID 字段完全是多余的；支持它的索引也是如此；愚蠢的AUTOINCREMENT也是如此；它是一个 PRIMARY KEY 的错误声明也是如此；您对它的任何期望都是错误的.

Therefore the ID field is totally superfluous; and so is the index that supports it; and so is the stupid AUTOINCREMENT; and so is the false declaration that it is a PRIMARY KEY; and any expectations you may have of it are false.

因此删除多余的 ID 字段.试试这个代码:

Therefore remove the superfluous ID field. Try this code:

    CREATE TABLE honest_table (
        name_first CHAR(30) NOT NULL,
        name_last  CHAR(30) NOT NULL

        CONSTRAINT PK 
        PRIMARY KEY ( name_last, name_first )
        )

    INSERT honest_table VALUES ( "Mickey", "Mouse" )  -- succeeds
    INSERT honest_table VALUES ( "Mickey", "Mouse" )  -- fails, as intended
    INSERT honest_table VALUES ( "Minnie", "Mouse" )  -- succeeds

    SELECT * FROM honest_table

工作正常，按预期工作，没有无关的字段和索引.

Works just fine, works as intended, without the extraneous fields and indices.

请记住这一点，并且每次都做对.

Please remember this, and do it right, every single time.

假教师

在这些结束时间，正如我们所建议的，我们将拥有许多.请注意，传播ID 列的教师"，凭借本文中的详细证据，根本不了解关系模型或关系数据库.尤其是那些写有关它的书的人.

False Teachers

In these end times, as advised, we will have many of them. Note well, the "teachers" who propagate ID columns, by virtue of the detailed evidence in this post, simply do not understand the Relational Model or Relational Databases. Especially those who write books about it.

事实证明，他们被困在 1970 年之前的 ISAM 技术中.这就是他们所理解的，也就是他们所能教的.他们使用 SQL 数据库容器，以便于访问、恢复、备份等，但内容是纯粹的记录归档系统，没有关系完整性、功能或速度.AFAIC，这是一个严重的欺诈行为.

As evidenced, they are stuck in pre-1970 ISAM technology. That is all they understand, and that is all that they can teach. They use an SQL database container, for the ease of Access, recovery, backup, etc, but the content is pure Record Filing System with no Relational Integrity, Power, or speed. AFAIC, it is a serious fraud.

当然，除了 ID 字段，还有几个关键的 Relational-or-not 概念，把它们加在一起，让我形成了如此严肃的结论.那些其他项目超出了本文的范围.

In addition to ID fields, of course, there are several items that are key Relational-or-not concepts, that taken together, cause me to form such a grave conclusion. Those other items are beyond the scope of this post.

一对特别的白痴目前正在对第一范式发起攻击.他们属于庇护所.

One particular pair of idiots is currently mounting an assault on First Normal Form. They belong in the asylum.

现在是您的其余问题.

有没有一种方法可以在不丢失自动增量功能的情况下创建关系表?

这是一个自相矛盾的句子.我相信你会从我的解释中明白，关系表不需要AUTOINCREMENT功能"；如果文件有 AUTOINCREMENT，则它不是关系表.

That is a self-contradicting sentence. I trust you will understand from my explanation, Relational tables have no need for AUTOINCREMENT "features"; if the file has AUTOINCREMENT, it is not a Relational table.

AUTOINCREMENT 仅适用于一件事:当且仅当您想在 SQL 数据库容器中创建一个 Excel 电子表格，其中包含名为 A, 的字段B, 和 C, 跨越顶部，并在左侧记录数字.在数据库术语中，这是 SELECT 的结果，数据的扁平化视图，即不是数据的源，即有组织(标准化).

AUTOINCREMENT is good for one thing only: if, and only if, you want to create an Excel spreadsheet in the SQL database container, replete with fields named A, B, and C, across the top, and record numbers down the left side. In database terms, that is the result of a SELECT, a flattened view of the data, that is not the source of data, which is organised (Normalised).

另一种可能(但不是首选)的解决方案可能是第一个表中还有另一个主键，它是用户的用户名，当然不是使用自动增量语句.这是不可避免的吗?

在技术工作中，我们不关心偏好，因为这是主观的，而且它一直在变化.我们关心技术正确性，因为这是客观的，不会改变.

In technical work, we don't care about preferences, because that is subjective, and it changes all the time. We care about technical correctness, because that is objective, and it does not change.

是的，这是不可避免的.因为这只是时间问题；错误数量；不能做"的次数；用户尖叫的次数，直到你面对事实，克服你的虚假声明，并意识到:

Yes, it is inevitable. Because it is just a matter of time; number of bugs; number of "can't dos"; number of user screams, until you face the facts, overcome your false declarations, and realise that:

• 确保用户行唯一，用户名唯一的唯一方法是在其上声明一个UNIQUE约束

• the only way to ensure that user rows are unique, that user_names are unique, is to declare an UNIQUE constraint on it

并去掉用户文件中的user_id或id

将user_name提升为PRIMARY KEY

是的，因为您在第三张桌子上的整个问题(并非巧合)都被消除了.

Yes, because your entire problem with the third table, not coincidentally, is then eliminated.

第三个表是一个关联表.唯一需要的键(主键)是两个父主键的组合.这确保了 行 的唯一性，这些行由它们的键标识，而不是由它们的 ID 标识.

That third table is an Associative Table. The only Key required (Primary Key) is a composite of the two parent Primary Keys. That ensures uniqueness of the rows, which are identified by their Keys, not by their IDs.

我警告你，因为教你实现ID字段的错误的同一个老师"，教你在关联表中实现ID字段的错误, 就像普通的表一样，它是多余的，没有任何用处，会引入重复项并导致混乱.它是双重多余的，因为提供的两把钥匙已经在那里，盯着我们的脸.

I am warning you about that because the same "teachers" who taught you the error of implementing ID fields, teach the error of implementing ID fields in the Associative Table, where, just as with an ordinary table, it is superfluous, serves no purpose, introduces duplicates, and causes confusion. And it is doubly superfluous because the two keys that provide are already there, staring us in the face.

由于他们不理解 RM 或关系术语，他们将关联表称为链接"或映射"表.如果它们有 ID 字段，它们实际上就是文件.

Since they do not understand the RM, or Relational terms, they call Associative Tables "link" or "map" tables. If they have an ID field, they are in fact, files.

ID 字段对于查找或引用表来说是特别愚蠢的事情.它们中的大多数都有可识别的代码，无需枚举其中的代码列表，因为代码是(应该)唯一的.

ID fields are particularly Stupid Thing to Do for Lookup or Reference tables. Most of them have recognisable codes, there is no need to enumerate the list of codes in them, because the codes are (should be) unique.

此外，将子表中的代码作为 FK 是一件好事:代码更有意义，而且通常可以节省不必要的连接:

Further, having the codes in the child tables as FKs, is a Good Thing: the code is much more meaningful, and it often saves an unnecesary join:

    SELECT ...
        FROM child_table           -- not the lookup table
        WHERE gender_code = "M"    -- FK in the child, PK in the lookup

代替:

    SELECT ...
        FROM child_table
        WHERE gender_id = 6        -- meaningless to the maintainer

或更糟:

    SELECT ...
        FROM child_table C         -- that you are trying to determine
        JOIN lookup_table L
            ON C.gender_id = L.gender_id
        WHERE L.gender_code = "M"  -- meaningful, known

请注意，这是无法避免的:您需要查找代码的唯一性和描述的唯一性.这是防止两列的每一重复的唯一方法:

Note that this is something one cannot avoid: you need uniqueness on the lookup code and uniqueness on the description. That is the only method to prevent duplicates in each of the two columns:

    CREATE TABLE gender (
        gender_code  CHAR(2)  NOT NULL,
        name         CHAR(30) NOT NULL

        CONSTRAINT PK 
            PRIMARY KEY ( gender_code )

        CONSTRAINT AK 
            UNIQUE ( name )
        )

完整示例

从你问题中的细节来看，我怀疑你有SQL语法和FK定义问题，所以我将给出你需要的整个解决方案作为例子(因为你没有给出文件定义):

Full Example

From the details in your question, I suspect that you have SQL syntax and FK definition issues, so I will give the entire solution you need as an example (since you have not given file definitions):

    CREATE TABLE user (                 -- Typical Identifying Table
        user_name  CHAR(16) NOT NULL,   -- Short PK
        name_first CHAR(30) NOT NULL,   -- Alt Key.1
        name_last  CHAR(30) NOT NULL,   -- Alt Key.2
        birth_date DATE     NOT NULL    -- Alt Key.3

        CONSTRAINT PK                   -- unique user_name
            PRIMARY KEY ( user_name )

        CONSTRAINT AK                   -- unique person identification
            PRIMARY KEY ( name_last, name_first, birth_date )
        )

    CREATE TABLE sport (                  -- Typical Lookup Table
        sport_code  CHAR(4)  NOT NULL,    -- PK Short code
        name        CHAR(30) NOT NULL     -- AK

        CONSTRAINT PK 
            PRIMARY KEY ( sport_code )

        CONSTRAINT AK 
            PRIMARY KEY ( name )
        )

    CREATE TABLE user_sport (           -- Typical Associative Table
        user_name  CHAR(16) NOT NULL,   -- PK.1, FK
        sport_code CHAR(4)  NOT NULL,   -- PK.2, FK
        start_date DATE     NOT NULL

        CONSTRAINT PK 
            PRIMARY KEY ( user_name, sport_code )

        CONSTRAINT user_plays_sport_fk
            FOREIGN KEY     ( user_name )
            REFERENCES user ( user_name )

        CONSTRAINT sport_occupies_user_fk
            FOREIGN KEY      ( sport_code )
            REFERENCES sport ( sport_code )
        )

在那里，PRIMARY KEY 声明是诚实的，它是一个主键；没有 ID; 没有 AUTOINCREMENT; 没有额外的索引；没有重复的行；没有错误的期望；没有相应的问题.

There, the PRIMARY KEY declaration is honest, it is a Primary Key; no ID; no AUTOINCREMENT; no extra indices; no duplicate rows; no erroneous expectations; no consequential problems.

这是与定义一起使用的数据模型.

Here is the Data Model to go with the definitions.

• 用户运动数据示例模型

如果您不习惯符号，请注意每一个小刻度、凹口和标记，实线与虚线，方角与圆角，都意味着非常具体的东西.请参阅IDEF1X 符号.

If you are not used to the Notation, please be advised that every little tick, notch, and mark, the solid vs dashed lines, the square vs round corners, means something very specific. Refer to the IDEF1X Notation.

一张图值一千字；在这种情况下，标准投诉图片的价值不止于此；一个坏的不值得画在纸上.

A picture is worth a thousand words; in this case a standard-complaint picture is worth more than that; a bad one is not worth the paper it is drawn on.

请仔细检查动词短语，它们由一组谓词组成.其余的谓词可以直接从模型中确定.如果不清楚，请询问.

Please check the Verb Phrases carefully, they comprise a set of Predicates. The remainder of the Predicates can be determined directly from the model. If this is not clear, please ask.

这篇关于SQL:创建具有 2 个不同 auto_increment 的关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！