Mazamal Ehsan Mazamal Ehsan - 5 months ago 36
SQL Question

mysql- foreign key referring to multiple rows in another table

I am working on mysql , trying to build a 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)
)


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

Answer

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.

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.

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.