Tip #23 – How to create a reference to an alternate key

Sometimes, you want the reference between two tables to reference an alternate key and not the primary key. Here's how you can do it in Vertabelo.

In this example, we want to model cars and their owners using owner and car tables.

The car table has an alternate key consisting of the vin column:

We want the owner table to refer to the car table, but using the car table’s alternate key. Let’s start with a typical reference by switching to a reference in the toolbox and putting a line from the car table to the owner table:

Now, we have a reference using the car table’s primary key. Our current goal is to switch it to the alternate key.

Let’s change reference properties. Delete the id and car_id column pair from the reference:

Then add a new one – between the vin and car_id columns:

Finally, rename the car_id column in the owner table to car_vin and change its type to char(17), just like in the original vin column:

Now, our reference is using an alternate key.