MySQL :: 边缘情况 :: 混合类型外键 :: 可能还是梦想？
有一个我希望解决的边缘情况。
我们运行一份体育报告，记录除其他统计数据外，可能涉及非联赛/非名单对手的得分结果。我的意思是，在某些情况下，Foo 对手在我们的系统中没有（也永远不会）名册。
所有者并没有简单地省略对手球队的任何统计数据，而是很久以前就要求我们内置允许输入球员姓名的选项（而不是从现有名单的球员姓名下拉列表中选择 [playerID]）。因此，我们的名册表有一个 intplayerID PK 列，但我们的统计表（进攻和防守）有 varcharplayerID 列。
这允许我们在名册表上进行外连接，并获取匹配的记录，无论球队名册是否存在（如果不存在，我们只需打印出 varchar stat 表的playerID 作为玩家的姓名）。肯定是被黑了，但还是能完成工作。
现在，我在结果表和统计表上有一个 FK，因此如果游戏结果被删除，相关统计数据也会被删除。然而，我们缺少一个重要的约束：花名册>>统计数据。由于playerID列类型不匹配，我无法添加我需要填补约束空白的FK（即从名册中删除玩家，他/她的所有统计数据将被孤立，不好）。
关于如何解决这个问题有什么想法吗？
Got an edge case that I'm hoping to resolve.
We run a sport report that records, among other stats, box score results which may involve a non-league/non-roster opponent. By this I mean that in some cases Foo opponent does not have a roster (nor will they ever) in our system.
Rather than simply omitting any stats for the opponent team, the owner requested long ago that we build-in the option to allow typed in player names (vs. selecting [playerID] from dropdown list of player names for an existing roster). So, our roster table has an int playerID PK column, but our stats tables (offense & defense) have varchar playerID columns.
This allows us to do an outer join on the roster table and get matching records whether a team roster exists or not (if not we simply print out the varchar stat table playerID as the player's name). Hacked for sure, but does the job.
Now, I have an FK on the results table to the stats table, so if a game result is deleted, related stats are as well. However, we're missing an important constraint: roster >> stats. Since the playerID column types do not match, I am unable to add the FK I need to fill in the constraints gap (i.e. delete a player from roster and all of his/her stats will be orphaned, not good).
Any ideas for how to work around this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我的经验是，这是开发人员会遇到的问题，但在实践中通常不会出现太多。
删除玩家是一个实际用例吗？用户是否想返回查看游戏而不看到“历史”玩家姓名？
我建议进行两项更改：
按名称标记非活动状态，而不是删除。
My experience is that this is a problem that occurs to developers, but doesn't usually come up much in practice.
Is deleting players an actual use case? Does the user want to go back to look at a game and not see a 'historic' player name?
I would recommended two changes:
inactive flag by their name, instead of deleting.