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

问题描述

我有两个表，每个表都有自己的自动递增IDs，它们当然是主键。

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

第一个是您只能有一个自动递增的列，第二个是在我从这2个列中删除AUTO_INCREMENT语句时发生的，因此AQL不允许我将它们设为外键，因为类型匹配失败。

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

另一种可能的(但不是首选的)解决方案可能是在第一个表中有另一个主键，它是用户的用户名，当然不使用AUTO_INCREMENT语句。这是不可避免的吗？

提前感谢。

推荐答案

1概念

您误解了一些基本概念，由此带来的困难。我们必须首先解决概念，而不是您认为的问题，因此，您的问题将消失。

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

不，它们不是。这是一个常见的误解。问题肯定会接踵而至。

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

• 当然，在SQL中，您可以将任何字段声明为PRIMARY KEY，但这并不能神奇地将其转换为英语、技术或关系意义上的主键。你可以说出一只吉娃娃的名字，但这并不能把它变成一只罗特韦勒，它仍然是一只吉娃娃。与任何语言一样，SQL只是执行您给它的命令，它不理解PRIMARY KEY表示关系，它只是在列(或字段)上创建唯一索引。

• 问题是，由于已将ID声明为PRIMARY KEY，因此将其视为主键，您可能会认为具有主键的一些特性。除了ID值的唯一性之外，它没有任何好处。它不具备主键的任何性质，也不具备任何类型的关系键。它不是英语、技术或关系意义上的关键。声明非键为键，只会迷惑自己，只有在用户吐槽表里重复的时候才会发现有很大的问题。

2关系模型

2.1关系表必须有行唯一性

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

• 执行this code(MS SQL)并向自己证明。请不要简单地阅读并理解本文，然后继续阅读此答案的睡觉，在进一步阅读之前必须执行此代码。它有治疗价值。

  -- [1] Dumb, broken file
  -- Ensures unique RECORDS, allows duplicate ROWS
  
  CREATE TABLE dumb_file (
       id         INT       IDENTITY PRIMARY KEY, 
       name_first CHAR(30), 
       name_last  CHAR(30)
       )
  
  INSERT dumb_file VALUES
       ( 'Mickey', 'Mouse' ),
       ( 'Mickey', 'Mouse' ),
       ( 'Mickey', 'Mouse' )
  
  SELECT *
       FROM dumb_file
  

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

请注意，在您的报告中，唯一独一无二的是ID字段，它没有用户关心，没有用户看到，因为它不是数据，而是一些非常愚蠢的老师告诉您放入每个文件中的一些额外的胡说八道。您具有记录唯一性，但不具有行唯一性。

就数据而言(实际数据减去无关紧要的添加)，数据name_last和name_first可以在没有ID字段的情况下存在。一个人有名字和姓氏，额头上没有ID。

您正在使用的第二件让您感到困惑的事情是AUTOINCREMENT.如果您正在实现一个没有关系功能的记录归档系统，当然，它是有帮助的，您在插入记录时不必编写增量代码。但是，如果您实现的是关系数据库，那么它就没有任何作用，因为您永远不会使用它。SQL中有许多大多数人从未使用过的功能。

2.2：纠正措施

那么，如何将充满重复行的DUMB_FILE升级、提升到关系表，以便获得关系表的一些质量和好处呢？这需要三个步骤。

1. 您需要了解密钥

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

   在Codd的关系模型中：

   密钥由数据组成

   和

   表中的行必须唯一

   您的"；键"；不是由数据构成的。这是一些额外的、非数据的寄生虫，由您感染了您的老师的疾病引起的。认识到这一点，并允许自己拥有上帝给你的全部心智能力(请注意，我并不是要求你以孤立、零散或抽象的术语来思考，数据库中的所有元素必须相互集成)。

   仅从数据根据数据组成真正的密钥。在这种情况下，只有一个可能的键：(name_last, name_first).

2. Try this code，声明数据的唯一约束：

   -- [2] dumb_file fixed, elevated to table, prevents duplicate rows
   -- still dumb
   CREATE TABLE dumb_table (
       id         INT       IDENTITY PRIMARY KEY, 
       name_first CHAR(30), 
       name_last  CHAR(30),
       CONSTRAINT UK
           UNIQUE  ( name_last, name_first )
       )
   INSERT dumb_table VALUES
       ( 'Mickey', 'Mouse' ),
       ( 'Minnie', 'Mouse' )
   
   SELECT *
       FROM dumb_table
   
   INSERT dumb_table VALUES
       ( 'Mickey', 'Mouse' )
   

   现在我们具有行唯一性。这就是发生在大多数人身上的顺序：他们创建了一个允许复制的文件；他们不知道为什么复制会出现在下拉列表中；用户尖叫；他们调整文件并添加索引以防止复制；他们转到下一个错误修复。(他们这样做可能是正确的，也可能是错误的，那是另一回事。)

3. 第二层。给那些思考超越解决方案的人。既然我们现在有了行唯一性，那么ID字段的目的究竟是什么，为什么还要有它呢？哦，因为吉娃娃叫腐烂，我们不敢碰它。

   声明为PRIMARY KEY是假的，但仍然存在，引发念力和虚假预期。唯一的真正密钥是(name_last, name_fist),，并且此时它是备用密钥。

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

   因此，请删除多余的ID字段。Try this code：

   -- [3] Relational Table
   -- Now that we have prevented duplicate data, the id field 
   -- AND its additional index serves no purpose, it is superfluous,
   -- like an udder on a bull.  If we remove the field AND the 
   -- supporting index, we obtain a Relational table.
   
   CREATE TABLE relational_table (
       name_first CHAR(30), 
       name_last  CHAR(30),
       CONSTRAINT PK
           PRIMARY KEY ( name_last, name_first )
       )
   
   INSERT relational_table VALUES
       ( 'Mickey', 'Mouse' ),
       ( 'Minnie', 'Mouse' )
   
   SELECT *
       FROM relational_table
   
   INSERT relational_table VALUES
       ( 'Mickey', 'Mouse' )
   

工作正常，按预期工作，没有多余的字段和索引。

请记住这一点，并且每次都要做好。

2.3"假教师"

正如建议的那样，在这些结束时间，我们将拥有许多这样的产品。请注意，根据本文中的详细证据，传播ID列的&teachers"；根本不理解关系模型或关系数据库。尤其是那些写过关于它的书的人。

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

当然，除了ID字段之外，还有几个项是关键的关系或非关系概念，综合在一起，我得出了这样一个严肃的结论。这些其他项目不在本帖子的讨论范围内。

一对特殊的白痴目前正在对第一范式发动攻击。他们属于精神病院。

3个解决方案

现在进入您问题的睡觉。

3.1答案

是否有办法在不丢失自动增量功能的情况下创建关系表？

那是一个自相矛盾的句子。我相信您会从我的解释中理解，关系表不需要AUTOINCREMENT"；Feature"；；如果文件有AUTOINCREMENT，则它不是关系表。

AUTOINCREMENT或IDENTITY只适用于一件事：如果且仅当您想要在SQL数据库容器中创建一个Excel电子表格，顶部充满名为A,和B,和C,的字段，并在左侧记录数字。在数据库术语中，这是SELECT的结果，即数据的平面化视图，也就是而不是数据源，它是经过组织(标准化)的。

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

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

是的，这是不可避免的。因为这只是一个时间问题；错误数量；无法拒绝的数量；用户尖叫数量，直到您面对事实，克服您的错误声明，并意识到：

• 确保行和user_name唯一的唯一方法是对其声明UNIQUE约束

• 并删除用户文件中的user_id或id

• 将user_name提升为PRIMARY KEY

是的，因为您与第三个表有关的整个问题(而不是巧合)随后就消除了。

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

标识 我警告您这一点，因为告诉您实现ID字段的错误的&同一位教师，教导您实现关联表中的ID字段的错误，在关联表中，就像普通的表一样，它是多余的，毫无用处，引入重复，并导致念力。而且它是加倍多余的，因为提供的两个密钥已经在那里了，正盯着我们的脸。

因为他们不理解RM或关系术语，所以他们将关联表称为"；link"；或"；map"；表。如果它们有ID字段，则它们实际上是文件。

3.2更新查找表

ID字段对于查找或引用表做起来特别愚蠢。它们中的大多数都有可识别的代码，不需要枚举其中的代码列表，因为这些代码是(应该是)唯一的。

ENUM同样愚蠢，但原因不同：它将您锁定在不兼容SQL；中的反SQL方法功能中。

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

        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

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

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

            CONSTRAINT PK 
                PRIMARY KEY ( gender_code )

            CONSTRAINT AK 
                UNIQUE ( name )
            )

3.3：完整示例

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

    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;noAUTOINCREMENT;，没有多余的索引；没有重复的行；没有错误预期；没有后续问题。

3.4：关系数据模型

这里是与定义配套的数据模型。

• 作为PDF

• 如果您不习惯这种记法，请注意，每一个小刻度、凹槽和标记、实线与虚线、正方形与圆角，都意味着一些非常具体的东西。请参阅IDEF1X Notation。

• 一张图片胜过千言万语；在这种情况下，一张标准投诉的图片比千言万语更有价值；一张糟糕的图片不值得在上面画一张纸。

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

这篇关于SQL：使用2个不同的AUTO_INCREMENT创建关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！