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!)
-
MySQL 1 - Intro to MySQL
MySQL 2 - Installing MySQL
MySQL 3 - MySQL Workbench
MySQL 4 - Beginner Terms Part 1
MySQL 5 - Beginner Terms Part 2
MySQL 6 - Beginner Terms Part 3
MySQL 7 - One-to-One Relationship
MySQL 8 - One-to-Many Relationship
MySQL 9 - Many-to-Many Relationship
MySQL 10 - Summary of Relationships
MySQL 11 - Entity Integrity
MySQL 12 - Referential Integrity
MySQL 13 - Domain Integrity
MySQL 14 - Primary Key
MySQL 15 - Auto Increment (AUTO_INCREMENT)
MySQL 16 - Foreign Key
MySQL 17 - ON DELETE and ON UPDATE
MySQL 18 - Intro to Normalization
MySQL 19 - 1NF
MySQL 20 - 2NF
MySQL 21 - 3NF
MySQL 22 - Normalization Summary
MySQL 23 - Intro to Data Types
MySQL 24 - Important Data Types
MySQL 25 - CHAR Data Type
MySQL 26 - VARCHAR Data Type
MySQL 27 - TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
MySQL 28 - ENUM
MySQL 29 - SET Data Type
MySQL 30 - INT Data Type
MySQL 31 - DECIMAL Data Type
MySQL 32 - FLOAT and DOUBLE Data Types
MySQL 33 - Datetime, Date, Time Data Types
MySQL All-in-One Quick Concepts Tutorial Series (3 HOURS!)