识别非识别关系的利弊，反之亦然

问题描述：

Let's imagine simple real world customer-loan relationship scenario, where loan existence without customer is impossible, hence the relationship logically should be many-to-one identifying relationship with the following structure:

CREATE TABLE `customer` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE `loan` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT NOT NULL,
  `amount` FLOAT,
  `currency` VARCHAR(10),
  PRIMARY KEY (`id`, `customer_id`),
  CONSTRAINT `identifying_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE = InnoDB;

On the other hand, the same logic technically can be applied with many-to-one non-identifying mandatory relationship with the following structure:

CREATE TABLE `customer` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE `loan` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT NOT NULL,
  `amount` FLOAT,
  `currency` VARCHAR(10),
  CONSTRAINT `non-identifying_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE = InnoDB;

Question: What are the advantages and disadvantages of using identifying relationship over non-identifying relationship or vice versa? Are there any technical preferences choosing one over another?

NB. One of the disadvantage using identifying relationship is composite PRIMARY KEY, which are generally difficult to maintain.

For example PHP Doctrine ORM does not support operating on such composite key, where one id is auto generated and the second key (foreign key) is the identifier of parent entity.

让我们想象一下简单的现实世界客户 - 贷款 code>关系场景，其中没有客户的贷款存在是不可能，因此关系逻辑上应该是多对一识别关系 strong>，具有以下结构： p>

  CREATE TABLE`customer`（
  id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY，
`name`VARCHAR（50）
）ENGINE = InnoDB; 
 
CREATE TABLE`love`（
`id` INT NOT NULL AUTO_INCREMENT，
`customer_id` INT  NOT NULL，
`value`FLOAT，
`currency`VARCHAR（10），
 PRIMARY KEY（`id`，`customer_id`），
 CONSTRAINT`identification_fk` FOREIGN KEY（`customer_id`）REFERENCES`客户 `（`id`）
）ENGINE = InnoDB; 
  code>  pre> 
 
 另一方面，技术上可以应用相同的逻辑 many-to- 一个非识别强制关系 strong>具有以下结构： p> 
 
 
  CREATE TABLE`customer`（
`id` INT NOT NULL AUTO_INCREMENT PRI  MARY KEY，
`name`VARCHAR（50）
）ENGINE = InnoDB; 
 
创建表`loan`（
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY，
`customer_id` INT NOT NULL，
  n`quant`FLOAT，
`currency`VARCHAR（10），
 CONSTRAINT`non-identif_fk` FOREIGN KEY（`customer_id`）REFERENCES`customer`（`id`）
）ENGINE = InnoDB; 
   pre> 
 
  问题：使用识别关系优于非识别关系有哪些优缺点，反之亦然？ 是否有任何技术偏好相互选择？ strong>  p> 
 
 
 NB。 使用标识关系 code>的缺点之一是 composite PRIMARY KEY  code>，它们通常很难维护。 p> 
 
 
例如 PHP  Doctrine ORM  code>不支持对这样的复合键进行操作，其中一个id是自动生成的，第二个键（外键）是父实体的标识符。 p> 
  div>

答

If you have an auto_increment column, then that should be the primary key. In general, I avoid composite primary keys. They just introduce scope for error in foreign key definitions and join conditions. You also point out the limitation when using other tools.

I would expect this question for an n-m relationship. That is one case where there is a good argument for a composite primary key. However, in your case, loans have only one customer, so the second method seems more "correct".

答

Meanwhile I read about the difference between identifying relationships and non-identifying relationships.

In your example, you have a many to one relationship. As such, the loans do not qualify for an identifying relationship, because the customer id is not sufficient to identify a loan. Thus the relationship is non-identifying.

If each customer can have only one loan, there would be a one to one relationship between the loans and the customers. The customer id would be sufficient to identify a loan, thus we have an identifying relationship. In this case, it would be a good choice to set the customer_id column of the loans table as a primary key.

Identifying relationships are also used with the link table in a many to many relationship.

识别非识别关系的利弊，反之亦然

相关推荐