Lucas Lucas - 10 days ago 6
SQL Question

One-to-many SQL relationship on keys with different types

For example, how can I create a one-to-many relationship between tables

InsuranceCo
and
Vehicle
, where the primary keys of each are
InsuranceCo.id
and
Vehicle.licencePlate
?

My attempt in creating the one-to-many relationship using a foreign key is this:

CREATE TABLE InsuranceCo (
id int PRIMARY KEY,
phone int
)

CREATE TABLE Vehicle (
licencePlate CHAR(10) PRIMARY KEY REFERENCES InsuranceCo(id),
year int
)


Will this work? If not, how can I create the one-to-many relationship when keys have different types?

Answer

This isn't how one-to-many relationships work at all. You don't just link two ids together, that is how one-to-one relations work (and no, those can't be done with different types, the values actually have to be the same). For one-to-many relationships, you need a separate value to reference the other table with.

You have to add a column -- for example insuranceCoId -- into the Vehicle table. Then any vehicle can have the id of the insurance company right there in the table. So data in might look like this:

InsuranceCo:

id   phone
1    800-744-2932
2    488-382-9332

Vehicle
LicencePlate    insuranceCoId    year
435yte          1                1995
328teo          1                2006
fd8tew          2                2008

As you can see, one insurance company is associated with many vehicles now.