外键引用另一个表中的多行

I am working with MySQL, trying to build an online vehicles database system. For this purpose, I created two tables: vehicles and owners.

我正在使用MySQL,试图建立一个在线车辆数据库系统。为此,我创建了两个表:车辆和车主。

create table vehicles
(
  v_id int AUTO_INCREMENT PRIMARY KEY,
  v_reg_number varchar(255) unique,
  v_engine_number varchar(255) unique,
  v_chassis_number varchar(255) unique,
  v_type varchar(255),
  v_manufacturer varchar(255),
  v_model_year varchar(255),
  v_power varchar(255),
  v_origin varchar(255) 
)

create table owners
(
  o_id int AUTO_INCREMENT PRIMARY KEY,
  v_id int,
  o_name varchar(255),
  o_father_name varchar(255),
  o_cnic varchar(255) unique,
  o_dob varchar(255),
  o_gender varchar(255),
  FOREIGN KEY(v_id) REFERENCES vehicles(v_id)
)

So one owner may have more vehicles registered in his name. My question is how can multiple IDs, referring to the vehicles table, be saved in owners.v_id?

因此,一个车主可能会有更多车辆以他的名义登记。我的问题是如何将多个ID(引用车辆表)保存在owners.v_id中?

1 个解决方案

#1

The usual solution to this would be to create an intersection table:

通常的解决方案是创建一个交集表:

CREATE TABLE vehicles_owned
(
  o_id INT,
  v_id INT,
  PRIMARY KEY (o_id, v_id),
  FOREIGN KEY (o_id)
    REFERENCES owners (o_id),
  FOREIGN KEY (v_id)
    REFERENCES vehicles (v_id)
);

You then drop v_id from owners.

然后从所有者中删除v_id。

This table allows each owner to own multiple vehicles, and each vehicle to have multiple owners. If you want to enforce a one-owner-per-vehicle constraint, add a UNIQUE index to vehicles_owned.v_id.

该表允许每个所有者拥有多个车辆,并且每个车辆拥有多个车主。如果要强制执行每车辆一个拥有者的约束,请向vehicles_owned.v_id添加UNIQUE索引。

EDIT: Of course, if you want to enforce a one-owner-per-vehicle constraint, you could also simply add o_id to vehicles as a foreign key, and not bother with the intersection table.

编辑:当然,如果你想强制执行一个车主的一个车主约束,你也可以简单地将o_id作为外键添加到车辆,而不是打扰交叉表。

#1