In this blog post I will walk through the fundamentals of an SQL dataset: tables, primary keys, foreign keys and table linkages.
The above is the entity relationship diagram of our data set. We have two linked tables: directors and movies.
In SQL, every table must have a primary id column. Every row in the table must have a unique identifying primary key in this column. No repeating or null values are allowed. In directors the primary key is called id. In movies the primary key is also called id.
A table can also have foreign keys which link to another table by pointing to the other table's primary key. These foreign keys can be repeated or null (if the row doesn't match with any rows on the other table).
In movies the foreign key is director_id, which connects to directors' primary id column.
Here are example fields that could be in each table:
directors
movies
In the next blog post we will look at how some basic SQL queries work with this dataset.