MySQL 一个表可以有两个外键吗？

Can a table have two foreign keys?

提问by Rav

I have the following tables (Primary key in bold. Foreign key in Italic)

我有以下表格（粗体的主键。斜体的外键）

Customer table

客户表

• ID---Name---Balance---Account_Name---Account_Type

• ID---名称---余额--- Account_Name--- Account_Type

Account Category table

账户类别表

• Account_Type----Balance

• Account_Type----余额

Customer Detail table

客户详细信息表

• Account_Name---First_Name----Last_Name---Address

• Account_Name---First_Name----Last_Name---地址

Can I have two foreign keys in the Customer table and how can I implement this in MySQL?

我可以在 Customer 表中有两个外键，我如何在 MySQL 中实现它？

---

---

Updated

更新

I am developing a web based accounting system for a final project.

我正在为最终项目开发基于网络的会计系统。

Account Category

账户类别

Account Type--------------Balance

账户类型--------------余额

Assets
Liabilities
Equity
Expenses
Income

资产
负债
权益
费用
收入

Asset

资产

• Asset_ID-----Asset Name----Balance----Account Type

• Asset_ID-----资产名称----余额----账户类型

Receivable

应收账款

• Receivable_ID-----Receivable Name-------Address--------Tel-----Asset_ID----Account Type

• Receivable_ID-----Receivable Name-------Address--------Tel----- Asset_ID----账户类型

Receivable Account

应收账款

• Transaction_ID----Description----Amount--- Balance----Receivable_ID----Asset_ID---Account Type

• Transaction_ID----Description----Amount--- Balance---- Receivable_ID---- Asset_ID---账户类型

I drew the ER(Entity relationship) diagram using a software and when I specify the relationship it automatically added the multiple foreign keysas shown above. Is the design not sound enough?

我使用软件绘制了ER（实体关系）图，当我指定关系时，它会自动添加多个外键，如上所示。设计不够健全？

回答by

Yes, MySQL allows this. You can have multiple foreign keys on the same table.

是的，MySQL 允许这样做。同一张表上可以有多个外键。

Get more details here FOREIGN KEY Constraints

在此处获取更多详细信息FOREIGN KEY Constraints

回答by user2915443

create table Table1
(
  id varchar(2),
  name varchar(2),
  PRIMARY KEY (id)
)


Create table Table1_Addr
(
  addid varchar(2),
  Address varchar(2),
  PRIMARY KEY (addid)
)

Create table Table1_sal
(
  salid varchar(2),`enter code here`
  addid varchar(2),
  id varchar(2),
  PRIMARY KEY (salid),
  index(addid),
  index(id),
  FOREIGN KEY (addid) REFERENCES Table1_Addr(addid),
  FOREIGN KEY (id) REFERENCES Table1(id)
)

回答by Marcelo Cantos

The foreign keys in your schema (on Account_Nameand Account_Type) do not require any special treatment or syntax. Just declare two separate foreign keys on the Customer table. They certainly don't constitute a composite key in any meaningful sense of the word.

架构中的外键（onAccount_Name和Account_Type）不需要任何特殊处理或语法。只需在 Customer 表上声明两个单独的外键。在任何有意义的意义上，它们当然不构成复合键。

There are numerous other problems with this schema, but I'll just point out that it isn't generally a good idea to build a primary key out of multiple unique columns, or columns in which one is functionally dependent on another. It appears that at least one of these cases applies to the ID and Name columns in the Customer table. This allows you to create two rows with the same ID (different name), which I'm guessing you don't want to allow.

此模式还有许多其他问题，但我只想指出，从多个唯一列或其中一个列在功能上依赖另一个列中构建主键通常不是一个好主意。似乎这些情况中的至少一种适用于 Customer 表中的 ID 和 Name 列。这允许您创建具有相同 ID（不同名称）的两行，我猜您不想允许这样做。

回答by momo

CREATE TABLE User (
user_id INT NOT NULL AUTO_INCREMENT,
userName VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
userImage  LONGBLOB NOT NULL, 
Favorite VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id)
);

and

和

CREATE TABLE Event (
    EventID INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (EventID),
    EventName VARCHAR(100) NOT NULL,
    EventLocation VARCHAR(100) NOT NULL,
    EventPriceRange VARCHAR(100) NOT NULL,
    EventDate Date NOT NULL,
    EventTime Time NOT NULL,
    EventDescription VARCHAR(255) NOT NULL,
    EventCategory VARCHAR(255) NOT NULL,
    EventImage  LONGBLOB NOT NULL,     
    index(EventID),
    FOREIGN KEY (EventID) REFERENCES User(user_id)
);