mysql 与主/外键混淆以获得给定结果

Question

create table users (

    id int,

    username varchar(256) primary key,

    password varchar(256) not null




);


create table grades (
    id int not null auto_increment primary key,    
    username varchar(256) not null,    
    assignmentName varchar(256) not null,    
    mark int,  
    foreign key(username) references users(username)    
);

insert into users values 

(NULL, "a", "x"),
(NULL, "b", "x"),
(NULL, "c", "x");


insert into grades values

(null, "a", "a1", 10),
(null, "a", "a2", 20),
(null, "c", "a1", 30)

我想要这个:

用户

id   username   password
1    a          x 
2    b          x
3    c          x

成绩

id   username   assignmentname   mark
1    a          a1               10 
1    a          a2               20
3    c          a1               30

grades 表的 id 与用户关系与 users 表相同。无需对值进行硬编码。无论如何我可以做到这一点吗？我有点困惑你是否可以用 key 来做到这一点。

与当前代码它们没有任何关系。

Answer 1

您需要做两件事来解析表上的键:

1. 表users:使用数字列作为表的主键用户。长度为 256 的 VARCHAR 列对于首要的关键。

2. 表成绩:如果您已定义成绩的主键 表为 NOT NULL AUTO INCREMENT，为什么要尝试填充此表 SQL 中包含 NULL 的列。主键永远不能为 NULL。那么你 应该使用基于整数的列，称为gradeId或类似的列 这张表。

所以你的表变成:

CREATE TABLE users (
    userId INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(256) NOT NULL,
    password VARCHAR(256) NOT null
    PRIMARY KEY (userId)
)

CREATE TABLE grades (
    gradeId INT NOT NULL AUTO INCREMENT,    
    userId INT NOT NULL,    
    assignmentName VARCHAR(256) NOT NULL,    
    mark INT,  
    PRIMARY KEY (gradeId),
    CONSTRAINT FOREIGN KEY (userid) REFERENCES users (userId) ON DELETE NO ACTION ON UPDATE NO ACTION
)

这样，您将拥有有效的 1:M(一对多)数据库关系。

有两个考虑因素:

1. 实体完整性:确保表中的每一行都是唯一的。这意味着每一行至少由一列标识设置主键。

2. 参照完整性:这确保了外键的一致性在表之间的关系中。 a 中外键的任何值子表，必须在父表的主键中找到。在您的情况，外键引用的任何用户表 grade 必须在表 user 中具有匹配的主键。