MySQL from the scratch


MySQL 16 - Foreign Key


Hide/Show description

I recommend you watch the previous video before watching this one. A foreign key is a column that references another column. It basically says that every value inside of the column has to exist in another column. What kind of column does it reference? It must reference a column that is UNIQUE. That's because it has to connect back to a column that can uniquely identify a row. Almost always we will use a foreign key to reference a primary key in another column. I've said this before, but when we reference a column using a foreign key, we create what is known as a parent-child relationship. The column the foreign key is referencing is known as the parent and the foreign key is known as the child. This is a good time to talk about attributes. This is a different type of attribute than entities and attributes we've already discussed. Attributes are extra information you give MySQL about a column to tell the dbms how to treat the column. Two attributes that you have to consider are NOT NULL, and UNIQUE. Another name for these are constraints. We've mentioned that primary keys automatically have these constraints, but foreign keys do not. We have to decide if we want to add these column attributes. Let's discuss what happens when we label a foreign key with either of these attributes. The first attribute, NOT NULL, requires that every row have a value for the column NOT NULL is applied to. When we are talking about foreign keys, this means that every child has to have a parent. This is often times what we want, but not always. In databases, sometimes it is okay to create an orphan, as depressing as that is. The second attribute, UNIQUE, says that a parent can only have one child. You can call this exclusivity. Now, the important thing to note is that this is exclusivity for this column only. We could have numerous rows in a different table reference this primary key. It only applies to the column with the UNIQUE attribute. Should you use the UNIQUE constraint? Only if there is a one-to-one relationship between the parent and the child. If you want only one child per parent, use the UNIQUE constraint. If you want any number children to reference a particular primary key, do not use the UNIQUE constraint. How do you actually create a foreign key? We will create foreign keys when we get to the CREATE TABLE command, but essentially you will use a foreign key attribute and then tell the table and column that the column references. What happens when you try to delete the parent? Check out the next video! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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!)