你误解了一些基本概念,并且由此产生了困难.我们必须首先解决这些概念,而不是你所认识到的问题,因此,你的问题就会消失.

auto incremented IDs,which are of course primary keys.

不,他们不是.这是一种常见的误解.并且保证问题随之而来.

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

>当然,在sql中,您可以将任何字段声明为PRIMARY KEY,但这并不会将其神奇地转换为英语,技术或关系意义上的主键.你可以将奇瓦瓦命名为“Rottweiller”,但这并没有将它变成罗威威尔,它仍然是奇瓦瓦.像任何语言一样,sql只是执行你给它的命令,它不理解PRIMARY KEY意味着什么是关系,它只是在列(或字段)上敲击一个唯一索引.
>问题是,由于您已将ID声明为PRIMARY KEY,因此您将其视为主键,并且您可能期望它具有主键的某些特性.除了ID值的唯一性之外,它没有任何好处.它没有主键的任何特性,也没有任何关系密钥.它不是英语,技术或关系意义上的关键.通过声明非密钥成为密钥,您只会混淆自己,并且只有当用户抱怨表中的重复时才会发现存在严重错误.

关系表必须具有行唯一性

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

执行this code(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",but not intended

    SELECT * FROM dumb_file

请注意,您有重复的行.关系表需要具有唯一的行.进一步证明您没有关系表或任何一个关系表.

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

就数据而言(实际数据减去无关的添加),数据name_last和name_first可以在没有ID字段的情况下存在.一个人的名字和姓氏没有在他们的额头上盖上身份证.

你正在使用的第二件事就是AUTOINCREMENT.如果您正在实现一个没有关系功能的记录文件系统,当然,这很有用,您不必在插入记录时对增量进行编码.但是,如果您正在实现一个关系数据库,它根本没有用处,因为您永远不会使用它. sql中有许多功能,大多数人从不使用.

纠正措施

那么如何将充满重复行的dumb_file升级,提升到Relational表,以获得Relational表的一些特性和优点？这有三个步骤.

>你需要了解Keys

>既然我们已经从1970年代的ISAM文件发展到关系模型,你需要了解关系密钥.也就是说,如果您希望获得关系数据库的好处(完整性,功能,速度).

E F Coo​​d博士在他的RM中宣称：

a key is made up from the data

和

the rows in a table must be unique

您的“密钥”不是由数据组成的.这是一些额外的非数据寄生虫,由您感染“老师”的疾病引起.认识到这一点,让自己拥有上帝给你的全部心智能力(注意我不要求你以孤立或零碎或抽象的方式思考,数据库中的所有元素必须相互整合).从数据中组成一个真正的密钥,并且只从数据中组成.在这种情况下,只有一个可能的密钥：(name_last,name_first).
> Try this code,声明对数据的唯一约束：

     CREATE TABLE dumb_table (
        id         INT      NOT NULL  IDENTITY  PRIMARY KEY,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

现在我们有行唯一性.这是大多数人发生的序列：他们创建一个允许欺骗的文件;他们不知道为什么欺骗会出现在下拉中;用户尖叫;他们调整文件并添加索引以防止欺骗;他们进入下一个bug修复. (他们可能正确或不正确,这是一个不同的故事.)
>第二级.思考那些超越修复的人 – 它.既然我们现在有行唯一性,那么天堂的名字是ID字段的目的,为什么我们甚至拥有它？哦,因为奇瓦瓦被命名为Rotty,我们不敢触摸它.

它是PRIMARY KEY的声明是错误的,但它仍然存在,导致混淆和错误的期望.唯一真正的Key是(name_last,name_fist),此时它是一个备用键.

因此ID字段完全是多余的;支持它的索引也是如此;愚蠢的AUTOINCREMENT也是如此;虚假声明它是一个主要的关键;你对它的任何期望都是错误的.

因此,删除多余的ID字段. 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",as intended
    INSERT honest_table VALUES ( "Minnie","Mouse" )  -- succeeds

    SELECT * FROM honest_table

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

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

假教师

在这些结束时间,按照建议,我们将拥有其中许多.请注意,根据本文中的详细证据,传播ID列的“教师”根本不理解关系模型或关系数据库.特别是那些写书的人.

如证据所示,他们陷入了1970年以前的ISAM技术.这就是他们所理解的一切,而这就是他们所能教导的一切.它们使用sql数据库容器,以便于访问,恢复,备份等,但内容是纯记录文件系统,没有关系完整性,功能或速度. AFAIC,这是一个严重的欺诈行为.

当然,除了ID字段之外,有几个项目是关键的关系或非关键概念,它们结合在一起,使我形成如此严肃的结论.其他项目超出了本文的范围.

一对特殊的白痴目前正在对First Normal Form进行攻击.他们属于庇护所.

回答

现在问你的其余部分.

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

这是一个自相矛盾的句子.我相信你会从我的解释中理解,关系表不需要AUTOINCREMENT“功能”;如果文件具有AUTOINCREMENT,则它不是Relational表.

AUTOINCREMENT仅适用于一件事：当且仅当您想要在sql数据库容器中创建Excel电子表格时,在顶部填充名为A,B和C的字段,并在左侧记录数字.在数据库术语中,这是SELECT(数据的展平视图)的结果,而不是数据源,它是有组织的(规范化的).

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?

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

是的,这是不可避免的.因为这只是时间问题;错误数量;数量“不能dos”;用户尖叫的数量,直到你面对事实,克服你的虚假声明,并意识到：

>确保用户行唯一的唯一方法是user_names是唯一的,就是在它上面声明一个UNIQUE约束
>并删除用户文件中的user_id或id
>将user_name提升为PRIMARY KEY

是的,因为你的第三个表的整个问题,而不是巧合,然后被消除.

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

我正在警告你,因为同样的“老师”教你实现ID字段的错误,教会在关联表中实现ID字段的错误,就像普通表一样,它是多余的,没有任何意义,引入重复,并导致混淆.它是双重多余的,因为提供的两把钥匙已经存在,盯着我们.

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

查找表

对于查找或引用表,ID字段尤其愚蠢.它们中的大多数具有可识别的代码,因此不需要枚举其中的代码列表,因为代码(应该)是唯一的.

此外,将子表中的代码作为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 )
        )

完整的例子

根据您问题中的详细信息,我怀疑您有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声明是诚实的,它是一个主键;没有身份证;没有AUTOINCREMENT;没有额外的指数;没有重复的行;没有错误的期望;没有相应的问题.

数据模型

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

> Example User Sport Data Model
>如果您不习惯符号,请注意每个小刻度,缺口和标记,实线与虚线,方形与圆角,意味着非常具体的东西.请参阅IDEF1X Notation.
>一张图片胜过千言万语;在这种情况下,标准投诉图片的价值不止于此;一个坏的不值得它所用的论文.
>请仔细检查动词短语,它们包含一组谓词.其余的Predicates可以直接从模型中确定.如果不清楚,请询问.