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.
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 is also available for references.