外键引用另一个表中的多行 [英] Foreign key referring to multiple rows in another table

问题描述

我正在与MySQL合作，试图构建一个在线车辆数据库系统.为此，我创建了两个表:车辆和所有者.

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

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)
)

因此，一个车主可能会用他的名字注册更多的车辆.我的问题是，如何将引用 vehicles 表的多个ID保存在 owners.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?

推荐答案

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

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)
);

然后，从所有者中删除 v_id .

此表允许每个所有者拥有多个车辆，并且每个 vehicle 具有多个所有者.如果要强制每个车主一个约束，请将 UNIQUE 索引添加到 vehicles_owned.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.

当然，如果您要强制每个车主一个人约束，则还可以简单地将 o_id 添加到 vehicles 作为外键，而不用担心相交表.

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.

这篇关于外键引用另一个表中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！