SQL:使用2个不同的auto_increment创建关系表 [英] SQL: Creating a relation table with 2 different auto_increment

问题描述

我有2个表，两个表都有自己的自动递增的ID，这些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中，您可以将 any 字段声明为PRIMARY KEY，但这并不能神奇地将其转换为英语，技术或关系型的主键.感官.您可以将奇瓦瓦州命名为"Rottweiller"，但这并不能将其转换为Rottweiller，它仍然是奇瓦瓦州.像任何语言一样，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 的唯一性外，它没有任何好处.它没有主键的质量，也没有任何关系密钥的质量.它不是英语，技术或关系意义上的关键.通过将非密钥声明为密钥，您只会混淆自己，并且您会发现只有当用户抱怨表中的重复项时，才存在严重错误.

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

Execute this code (MS SQL 2008) and prove it to yourself. Please do not simply read this and understand it, and then proceed to read the rest of this Answer, this code must be executed before reading further. It has curative value.

    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字段，没有用户在乎，没有用户看到，因为它不是数据，这是一些非常愚蠢的老师"的废话.告诉你放入每个文件.您具有记录唯一性，但没有 row 唯一性.

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字段而存在.一个人的名字和姓氏，其额头上没有标识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.

那么，您如何将具有重复行的dumb_file升级，提升为Relational表，以便获得Relational表的某些质量和好处?为此，需要执行三个步骤.

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. 您需要了解按键

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.

E F Coo​​d博士在其 RM 中声明:

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

密钥由数据组成

和

表中的行必须唯一

您的密钥"不是由数据组成的.这是由于您感染了老师"疾病而引起的一些其他非数据寄生虫.就这样认识到这一点，并让自己拥有上帝赋予您的全部心理能力(请注意，我不要求您以孤立，零散或抽象的方式思考，数据库中的所有元素必须相互整合).仅根据数据由数据组成真实的密钥.在这种情况下，只有一个可能的键:(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).

> 尝试此代码 ，声明对数据的唯一约束:

Try this code, declare an unique constraint on the data:

     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字段的用途是天堂的名字，为什么还要拥有它呢?哦，因为吉娃娃叫罗蒂，我们害怕碰它.

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字段外，还有几个关键的关系或非概念的项目，这些项目加在一起，使我得出了这样一个严肃的结论.这些其他项目不在本文的讨论范围之内.

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.

现在剩下的问题要解决.

Now for the rest of your question.

是否可以在不丢失自动增量功能的情况下创建关系表?

那是一个自相矛盾的句子.我相信您将从我的解释中了解到，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的结果，即数据的扁平视图，即 not 数据的源，即有组织的(规范化).

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:

• 确保用户行是唯一的，user_names是唯一的唯一方法是在其上声明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

and get rid of user_id or id in the user file

将user_name提升为PRIMARY KEY

是的，因为这样就消除了您对第三张桌子的整个问题，而并非巧合.

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

第三张表是关联表.唯一需要的键(主键)是两个父主键的组合.这样可以确保行的唯一性，这些行是通过其键而不是通过其IDs.

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

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

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创建关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！