We’ve got two tables – one to store cities and one to store continents. Now, we want to add a column called continent   to the City  table but we need it to be consistent with the names of continents in the Continent  table. To do that, we must use references.

Adding a new reference

To add a new reference click the Add new reference  button on the toolbox or press 4 on your keyboard.

Click on the table which you want to make a reference to and then drag your cursor onto the second table. When you release the mouse button, the reference will be created and a new foreign key column automatically added. If you don’t want foreign keys to be generated automatically, you can switch this feature off in your account preferences.

If you don’t like the column’s name generated by Vertabelo, it can be changed in Table Properties  without any problem. We will change it to just continent.

Reference properties

The reference’s name is automatically set to contain names of both columns but it’s completely OK to change it.

Cardinality is the range of possible answers for the question How many cities can be on the continent?  For this example, we will allow continents to have no cities, since there might not be any city in, for example, Antarctica in our database. The upper limit for the number of cities on the continent is * – infinitely many.

If you set the reference in the wrong direction, you can change it to the correct one by swapping the reference. Fortunately, in our case, the direction was good to begin with, so after the swap it changed to the wrong one.

We can add new pairs of columns to the reference or delete existing ones. If needed, columns’ roles may be set too.

We want to be ready for any typos in the Continents  table, so we are using cascade  in the Update constraint action  to let changes in Continents  also affect Cities. If a name is removed from the Continents  table, we want to change every appearance of that name in the Cities  table to NULL and that’s exactly what the Delete constraint action set null  will do for us.

We can change the reference’s line color in our model and set some additional properties, more specific to the chosen database engine.

SQL Preview

SQL Preview  is also available for references.