如何在MySQL中以编程方式创建一对零或一关系？

Question

如何在MySQL中以编程方式创建一对零或一关系？

mysqlsqldatabase-designforeign-keysrelational-database

4

我正在忙于一个项目，其中遇到的一个难题是：

我有一个预订表，可能会导致发票被开具，也可能不会（因为一些无关紧要的事情，比如取消）。我该如何强制执行一对零或一（在发票方面）的关系？到目前为止，我的做法如下：

CREATE TABLE IF NOT EXISTS `booking` (    
   `booking_id` int(11) NOT NULL AUTO_INCREMENT,   
   `voucher_id` int(11) NOT NULL,  
   `pickup_date_time` datetime NOT NULL, ...  
   PRIMARY KEY (`booking_id`,`voucher_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

然后，随后：

  CREATE TABLE IF NOT EXISTS `invoice` (  
  `booking_id` int(11) NOT NULL,  
  `voucher_id` int(11) NOT NULL,  
  `invoice_number` int(11) NOT NULL,  
  `paid` tinyint(1) NOT NULL,  
  PRIMARY KEY (`booking_id`,`voucher_id`),  
  UNIQUE KEY `invoice_number` (`invoice_number`),  
  KEY `voucher_id` (`voucher_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

voucher_id 是系统中我使用的另一个标识。而 invoice_number 也是在 PHP 中生成的，因此不相关。

非常感谢您的帮助！

- iLikeBreakfast

只是好奇，为什么invoice_number不是主键，而booking_id、voucher_id是唯一的？这似乎有点不合逻辑，你的外键可能会指向invoice_number吧？ - thaJeztah

1

此外，在预订中的复合键 - 这意味着只要我输入不同的voucher_id，我可以有两个相同的booking_id？您应该将booking_id作为PK，并添加一个UNIQUE约束条件到voucher_id上，以强制执行一张凭证只能使用一次。或者将'booking_id'带到凭证表中（是的，这将是可空的）。即尚未使用的凭证将具有null作为booking_id，一旦它们被使用，就会有现有的booking_id。 - thaJeztah

2

不，它不能；首先，自动编号只是为了辅助创建新号码，它并不能防止您手动插入一个具有booking_id的记录，这将允许重复的booking_id。此外，预订需要凭证吗？最后，如果您想要“无间隙”的booking_id（即不要0001、0005、0006），也不要依赖AutoNumber，因为插入失败也可能会增加计数器。在我看来，“booking_id”是PK，“voucher_id”是“凭证”的外键（如果预订不需要凭证，则应为空）。 - thaJeztah

1

同时标记我的第一个评论；发票也适用；PK 应该是 invoice_id，现在预订的 PK 是 booking_id，您可以删除 voucher_id 列。最后要考虑的一件事是，如果一个预订可以使用多个优惠券（例如，如果允许使用优惠券进行折扣并且可以组合使用），则将它们存储为 n:m 关系（HasAndBelongsToMany）在连接表 bookings_vouchers 中。 - thaJeztah

很高兴我能够给你一些“正确”的方向指引。没有什么是绝对正确的，这取决于你的使用情况，但我认为这些更改在任何情况下都是正确的。 - thaJeztah

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Branko Dimitrijevic · Accepted Answer

这更或多或少只是对@thaJeztah在评论中提出的建议进行系统化，但无论如何，以下是内容：

CREATE TABLE voucher (
    voucher_id int(11) PRIMARY KEY
   -- Etc...
);

CREATE TABLE booking (
    booking_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    voucher_id int(11) REFERENCES voucher (voucher_id),
    pickup_date_time datetime NOT NULL
   -- Etc...
);

CREATE TABLE invoice (
    invoice_number int(11) NOT NULL PRIMARY KEY,
    booking_id int(11) NOT NULL UNIQUE REFERENCES booking (booking_id),
    paid tinyint(1) NOT NULL
   -- Etc...
);

最小基数：可以有预订但没有发票。然而，由于非空字段 invoice.booking_id 上的 FK，不能有发票没有预订。

最大基数：由于 invoice.booking_id 上的 UNIQUE 约束，一个预订不能连接到多个发票。一个发票也不能连接到多个预订，因为一个字段（在一行中）不能包含多个值。

因此，预订和发票之间的关系是“一对零或一”。

或者，将所有内容放在一个表中，使用可为空的字段随着预订的进展逐步填充。