mysql引用其他表主键,MySQL外键使用多个字段来引用另一个表中的主键

I want to reference 2 of my columns in one table to a primary key from another table. Here is how it looks like in the db structure:

Users

uid (INT) name (VARCHAR)

1 John Doe

2 Jane Doe

SystemProcesses

uid (INT) name (VARCHAR)

1 Hitman

2 Cron

Logs

uid (INT) modelType (VARCHAR) modelUID (INT) Action

1 Users 2 Jane Doe did this

2 Users 1 John Doe did that

3 SystemProcesses 1 Hitman just killed John Doe

How do I reference modelType and modelUID in Logs table to those Users and SystemProcesses?

If it's not possible, what is the the alternative?

解决方案

Don't use the same column for both foreign keys. This is sometimes called polymorphic associations, and it breaks rules of good database design.

It should be a clue that it's a bad design, that a FOREIGN KEY constraint supports only one referenced table. There is no support for polymorphic associations in standard SQL.

Instead, create two columns, one for a reference to Users, the other for a reference to SystemProcesses. One column per referenced table.

Logs

uid (INT) UsersID (INT) SystemProcessesID (INT) Action

1 2 NULL Jane Doe did this

2 1 NULL John Doe did that

3 1 1 Hitman just killed John Doe

If there is no relevant reference for either the Users or SystemProcesses column, use NULL to indicate there's no applicable value.