mysql - 一张 table 一对多

Question

我正在尝试使用一张表创建一对多关系。这可能吗？

create table user(id int primary key auto_increment not null,                                                                                                                      
created_by int default null                                                                                                                                                        
)ENGINE=INNODB;                                                                                                                                                                    

alter table user add foreign key (created_by) references user(id) ON DELETE SET NULL ON UPDATE CASCADE;                                                                            

insert into user (id) VALUES(1);                                                                                                                                                   
insert into user (id, created_by) VALUES (2,1);   

现在，当我删除 id=1 的用户时，created_by 的值会自动更改为 NULL，正如我预期的那样。

但是当我用 id=1 更改用户的 id 时，我得到这个错误

mysql> update user set id=2 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`jrt`.`user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)

我该如何解决这个问题？更新后，我希望对列 created_by 进行级联更改。

Answer 1

key 不是用来更改的，它们是用来“识别”的。因此，id 为 2 的用户在逻辑上与 id 为 1 的用户是不同的用户。在您创建唯一用户的那一刻，它的主键在该用户的生命周期内应该是相同的。

所以这确实是一个设计问题。您需要质疑为什么要更改特定用户的 ID。可能您想要的既是固定的标识键又是您可以更改的另一个标识符(不是键，仅存在于用户表中)。

[更新了更多信息]这里有一个关于关系数据库设计基础的资源(网上有很多资源)。 http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

相关部分是“表、唯一性和键”。

Fabian Pascal, in his book SQL and Relational Basics, notes that the decision should be based upon the principles of minimality (choose the fewest columns necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users).

就个人而言，我会在稳定性方面走得更远；尝试选择一个永不改变的 key 。例如，“电子邮件”对于用户来说是一个糟糕的 key 选择，因为用户可以更改他们的电子邮件。如果您选择一个键，例如内部生成的编号或来自人事系统的唯一标识符，那么您永远不必担心它会更改并将此更改迁移到其他表。

注意:在某些情况下，您可能需要“一次性”更改主键。这最好使用一些手动 SQL 语句来完成(删除第一个用户并使用不同的 key 创建相同的第二个用户)。它不应该是数据库设计的一部分，级联更新的自动化特性暗示了这一点。

另请参见: When to use "ON UPDATE CASCADE"

另请注意: http://forums.mysql.com/read.php?136,391782,391782