Introduces primary keys, foreign keys, indexes, and unique constraints, and demonstrates their uses
- [Emma] Before we start reporting from multiple tables, we need to take a look at how tables are linked together. Not all fields are created equal. Some are optimized for searching on, for example. Not all tables are created equal, either. Some are big, some are small. Some contain reference data and hardly change. While others are constantly expanding, such as those containing sales data. I'm going to use the Sequila database to demonstrate some of these differences. And I'm going to look in the Rental Table for this.
If we look at the Structure Tab, we can see there are Key symbols against some of the fields. We've got one gold one, and several silver. And if we hover over the gold one, it says Primary. A Primary Key is a special kind of field, which uniquely identifies all the rows in the table. It can't be set to null and no two rows can have the same value. Let's just look at the data quickly. And we can see these are integers, and each row appears to be one higher than the row before. Now, we're not going to check all 16,000 you'll be please to know, but if we pop back, you can see that's right.
The data type is indeed Integer. And the field is set to Auto Increment. And what that means is whenever a new row is entered, the database itself goes and works out what the maximum is and adds one. It's a way of ensuring data quality. You could make a text field the Primary Key in theory, but I've never seen it done. Generally speaking, the Primary Key is an integer, and by convention, it's the first field in the table, and it's name is the Table Name, Rental, followed by underscore ID.
It's really a glorified row number, but it's crucial for linking tables together, as we're going to see. Now, next we've got Rental Date. And if we hover, the database software tells us that this is an Index. An Index is a field that's been flagged to the database as being a useful search column. Creating an index creates data that describes the table to help the database find rows faster. Now the downside is, that indexes themselves take up space and having too many can make your database run slowly.
So you would only put an index on a column that's likely to be searched on. The remaining three keys that we have here are Foreign Keys. Now you can't tell that by looking at them. If I hover, they just appear to be Indexes. But I can tell you, because I've looked, and I'll show you how in a second, that these are actually the Primary Keys of the Inventory Table, Customer Table, and Staff Tables respectively. If we go back to the Browse Tab, can you see that the font is in blue, rather than black for these three columns? And actually, if I click on one of them, it takes me through to the Inventory Table, to the corresponding row to the number that I clicked on.
Which was 367. Now this actually gives us a clue about what's special with Foreign Keys. Let's pop back to the Rental Table. There is a sense in which this Inventory ID field doesn't belong to the Rental Table. It actually belongs to the Inventory Table. So we couldn't enter any old value into these fields here. You couldn't put nine million, or minus four here, unless there was a corresponding entry in the Inventory Table.
The person who's populated this table, all they've done is selected a value from a set of master data that's actually kept in the Inventory Table. And that's the sense in which these fields are foreign. Their data doesn't really belong to the Rental Table at all. Now, if we go back to the Structure Tab. So, in this one table, we have three of the four main kinds of index or key. We've seen Primary Key, Searchable Index, and a Foreign Key.
Down here we can see that there's one more, which is called a Unique Constraint. Applying this to a field would mean that going forward, the database would reject any duplicate value. And you could only successfully apply it to a field that actually already contained unique values, otherwise, the database would reject the Unique Constraint entirely. So here is where you would set a Primary Key, Unique Constraint, and Index on a field. But there's no option to set a Foreign Key.
And that's because, Foreign Keys refer to other tables. So you can't just click on a Field Name and declare one, you need to provide a bit more information about how the two tables link together. If we click on Relation View, you see that there's three rows here, and each of these correspond to one of the Foreign Keys. By convention, a Foreign Key is given a name beginning with FK for Foreign Key. And then we've got the Table Name, and then we've got the name of the other table which is being linked in by this key.
And as I say, that's just convention. You could call them what you like. So on the left is the name of the key, and the behaviors that should be triggered when a relevant row is deleted or updated. And then in the middle here, where it says column, we have Customer ID, which is the field that's been selected from the Rental Table. And notice that there's only five fields listed here, even thought the Rental Table has seven fields. So we can actually only pick a field that has an Index created on it.
And that's true of MySQL. It may be different in different versions, different software. So, this section selects the appropriate field from the Rental Table and then these three drop downs tell the database software where to find the master data. Now, for this first Foreign Key, they should look in the Sequila database, the Customer Table, and then the Primary Key of the Customer Table, which is Customer ID. Similarly, the next one is in the Inventory Table, and the final one in the Staff.
Foreign Keys work very differently in different databases. But they usually implement some variation on this theme. To give an example of some of the differences, in a MySQL database when you create a Foreign Key, you automatically create an index on the column. And in other software products, this isn't true. So you could have a Foreign Key without having a corresponding Index. So here's a list of all the indexes in this table. Three of which are Foreign Keys, because we're in MySQL.
So let's go back to look at the remaining fields in this table. And here we have Return Date, which is just a field for storing when the film was returned. Now, we can infer that this is not a column often used for searching, because there's no index on it. Finally, we have the Last Update field. This is an audit field, and you'll often find at least one of these in a table. There are two giveaways to the fact that it's an audit field. The first is that it's the last field in the table, which again is just convention, but often holds true.
Second it has a data type of Time Stamp, rather than Date/Time, or one of the other manual entry types. And added to that, the clinching fact is that we have this behavior over here. On update, current time stamp. And what that means is, if anybody edits a row in this database, the last update field will be updated automatically. You'll often find a second audit field in tables such as these which contains the User ID of the person who committed the last change.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries