MySQL from the scratch

MySQL 11 - Entity Integrity

Hide/Show description

This video we are going to discuss entity integrity. As a review, what is integrity? When we say integrity we are talking about data integrity. Data integrity is a thing we strive for when we are building a database. It essentially means that our data is correct, as up to date as possible, not conflicting with others, and our database design is optimized to protect this. There are three categories of data integrity. The first type is known as entity integrity. Before we dive into integrity though, you should first understand our goal when storing data. We always try to focus on singularity and avoid plurality. This concept is known as the rule of one. When we create a table, we design it to store information about one kind of entity. So for example, we could have a table for cars at a car dealership. Now, we are using the word cars plural, but the concept of cars describes one type of thing. This is known as the entity type. They entity type should be of one type of thing. This is just one example of where we try to make things singular. You will see many more as we go through these integrity concepts. Entity integrity refers to the concept of the primary key. Specifically that the primary key should be UNIQUE, and NOT NULL. Imagine having two rows with the exact same data. Is it a duplicate, or two cars with the same information? This is a problem. The primary key is there to protect us from this. If we give some column that is unique to every row, then we will know that these describe two different entities. We often use surrogate keys. These are computer generated numbers just for the purpose of protecting our entity integrity. Some people use natural keys instead. A natural key is a key that uses something from the real world to force uniqueness. For example, we can give every use a username on a website to force uniqueness. I think it is good practice to be familiar with both surrogate and natural keys, but I think the majority of people use surrogate keys. In addition to the primary key being UNIQUE, it must also be NOT NULL. That means every entity has a value for the primary key. Your primary key also should never change. Think about it…if an ID of 7 points to a specific entity, and then the ID changes to 10…Are we talking about the same entity with a new ID, or a new entity altogether? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)