Join Gerry O'Brien for an in-depth discussion in this video Understanding table relationships, part of Querying Microsoft SQL Server 2012.
- View Offline
Now that we've seen how normalization separates our data out into multiple tables, we need to understand how those tables relate to each other. In other words, how can we get information back from each of these separate tables and show that there's some kind of a coherent relationship between the information that are in these separate tables? In order to figure this out, we need to focus on how the tables relate and what mechanism it is that we will use to determine the relationship between the data in the tables.
So again, we've normalized our database. We've separated the information out into tables. Now we need to consider, how are those tables related to each other? These relationships allow us to get a complete picture of the data. And to consolidate the results into the query when we are executing it against multiple table databases. A primary key serves a couple of purposes. In the database, it's really the mechanism that's used to ensure uniqueness in a table. So, we can have a primary key that exists on a single column or multiple columns and by creating that primary key in the table, we ensure uniqueness across all of the rows in the table, that way, we don't have duplicate rows of data, if you will, in the table.
Which again, is a part of what normalization is designed to overcome. However, primary keys are also considered the key, if you will, to getting at the records in a table. . Even though we can execute queries on single tables and never have to worry about the primary key. In other words, we don't include it in the query, we don't even see it sometimes in the query results, because we don't care what it is. When we start looking at relationships between the tables and pulling the data in from multiple tables, this is where the primary key factors in and becomes that key to getting at the records in the table.
So, as a result, we can consider primary keys to be only one part to the table relationship. Foreign keys are the other aspect or the other piece of it, when we start looking at pulling in the related data. Foreign keys are placed into a related table, as a means of insuring that we can relate the two tables together. Note that a foreign key doesn't create a uniqueness in the related table, so that's not the purpose of a foreign key. Again, the foreign key is designed to provide that relationship between the two tables.
The reason we call them foreign keys is because they are key that exists in another table, so they come from a foreign table, hence the reason we call them foreign keys. So, to get an idea of how the primary keys and foreign keys work together to relate the tables in a normalized database, let's take a quick step out, into SQL server. And, look at a couple of related tables. The first table we'll look at is the person table. And, in here we can see entities, where we have the person type, the name style, the title, the first name.
All of these values represent a person object in our database. The primary key is the business entity ID, but take note that it's listed as PK for primary key, and FK for foreign key, in this particular table. It's an integer value, and it's not null, so in other words we have to have a value in here, and you'll find that requirement on all primary and foreign keys in a database. So, the business entity ID is the primary key in the person table but it is also a foreign key in the reference table.
If we come up now and take a look at our employee table, because a person is a employee, this is how the relationship is established between these two tables. The business entity id is present in here as well. Even though it's also a primary key, in this table, it's still represented as a foreign key as well. So, that's the reason why you'll see primary key and foreign key indicated in the employee table on that business entity ID and again, on the person, sorry in the person table because it's a primary key in one table and a foreign key in another.
So, this establishes the relationship between these two tables. And using those two keys, the primary key foreign key relationship, when we execute our queries, we'll then have the ability to pull data from both tables as it relates to the person being an employee in the database. So again, keep in mind the foreign keys are the mechanism that provide a link between the tables and the foreign key exists as a primary key in the foreign table. Using this link will return data from multiple tables into one result set as long as the data is related.
- Writing SELECT queries
- Querying multiple tables
- Filtering text and duplicates
- Sorting and grouping query results
- Using SQL Server's built-in functions
- Writing subqueries
- Using common table expressions
- Programming with T-SQL
- Interpreting query performance data