CODEWITHSUNDEEP
sql
modulitos
modulitos

Reputation: 15814

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?

Upvotes: 0

Views: 230

Answers (4)

Andy
Andy

Reputation: 2354

I am considering having a many to many relationship for most(if not all) entities in my organisation.

Using your example:

CREATE TABLE InsuranceCo (
    Id int PRIMARY KEY,
    Phone int
)

CREATE TABLE Vehicle (
    Id int PRIMARY KEY,
    LicencePlate CHAR(10),
    Year int
)

CREATE TABLE VehiclesInInsuranceCo (
    Id int,
    VehicleId FOREIGN KEY REFERENCES Vehicle(Id),
    InsuranceCoId FOREIGN KEY REFERENCES InsuranceCo(Id)
)

If you are following you can see InsuranceCo and Vehicle exist completely independently of each other and can be populated by some automated script.

When you want to connect a vehicle to an insurance company, you would do this:

InsuranceCo
Id 1, Phone 07944555554
Id 2, Phone 07944555557

Vehicle
Id 1, LicensePlate K1NGS, Year 2016
Id 2, LicensePlate S0L1D, Year 2015

VehiclesInInsuranceCo
Id 1, VehicleId 1, InsuranceCoId 1
Id 2, VehicleId 2, InsuranceCoId 1

So from the dataset, we can see that there are 2 vehicles in insurance company 1 and no vehicles in insurance company 2.

If there are any possible problems in using this pattern, please let me know as in my mind, this solves regularly occurring problems in databases when tables become unmanageable because they have columns that were added later ad-hoc without much consideration of the null rows that will exist.

Upvotes: 0

Damien Black
Damien Black

Reputation: 5647

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.

Upvotes: 2

Koshera
Koshera

Reputation: 449

You can not have a one-to-many relationship with keys of different types. This is an example of poor database design. The licencePlate should not be a primary key or foreign key in that table. What will happen when someone renew their license plate and some records in other tables are related to the old one? You should change your design to something like this: 

CREATE TABLE Vehicle (
       vehicleId int PRIMARY KEY,
       insuranceId int,
       licencePlate CHAR(10), 
       year int,
       FOREIGN KEY (insuranceId) REFERENCES InsuranceCo(Id)
)

Make sure your primary keys are auto-incremented (or your application is handling them correctly). Use the insuranceId for the one to many relationship to InsuranceCo...

Upvotes: 1

Brian Driscoll
Brian Driscoll

Reputation: 19635

I'm assuming that in the one-to-many relationship, InsuranceCo will have a multiplicity of 1 and Vehicle will have a multiplicity of * (many).

In this case, you'll want to create an additional column on the Vehicle table of type int called InsuranceCoId, which will be a foreign key reference to the InsuranceCo table. You can then create said foreign key constraint on the Vehicle table itself:

ALTER TABLE Vehicle 
ADD CONSTRAINT FK_Vehicle_InsuranceCo 
FOREIGN KEY (InsuranceCoId) 
REFERENCES InsuranceCo (id)

Now, when you add vehicles to the system, you can add associated insurance company references.

The above will address the immediate question you have.

However, I believe your database design could be improved by adding an InsurancePolicy table that will create a many-to-many relationship between Vehicle and InsuranceCo, tied together with information specific to a policy (such as premium, deductible, etc).

Upvotes: 1

Related Questions