We're going to talk about database table associations now. How we can define them through SQL and JDBC commands. These associations usually depend on primary foreign key relationships between tables. What we're talking about here is that we have a foreign key on one table, let's call it the child table, and it must have a matching primary key on a parent table, or it actually can be null. This constraint between the parent and child table is maintained to ensure that we can navigate between the two tables using the foreign key, primary key relationships such as in a joint. It means that the reference from a row in one table to another table is valid. It also means that we cannot delete a record in the parent table. The one with the primary key while it still has rows in the child table whose foreign key points to that primary key. We will leverage these relationships through SQL to navigate between the two tables, such as a joint. We need to define a primary key first. I want to ensure my primary key is one unique and not null. We frequently use a surrogate key, a primary key generated by the database to ensure this uniqueness. Here, we've defined an ID of type int. It is generated as an identity column. An identity column will also increment on the database side. It's not going to be null, and it is our primary key. In defining such a key, the user itself generally will never insert a value in to the identity column. The database does it. Here when I load my employees, you can see that I'm not loading anything into the ID column, just FirstName, LastName, Hire_Date and Vacation_Days now, Mary and Jason now have vacation days. Thank goodness. If I execute this statement with these two strings SQL statements. When I execute a query against it to get the maximum ID from employee, the ID being generated by the database itself and I position myself at the first row, you will find that the max ID is two, the increment is taking place, so you do not set the ID when the private key is defined in this way. Now let's talk about foreign keys now. We've seen that the employee table has a primary key that's generated as Identity. The department table is not going to have a foreign key, Apple ID. When we load this table, we have to load a matching value in EMPL_ID that matches a primary key value on the employee table. Indeed physics and chemistry, one and one, looks like employee number 1 is both in the physics and chemistry department. You'll notice I haven't loaded the DEPT_ID in department, and you guess why? Because my DEPT_ID again is generated always as Identity. We're letting the database ensure the uniqueness of each row in the department. The foreign key EMPL_ID we have set. That foreign key does not have to be unique because as things stand at the moment an employee can be in many departments, but the DEPT_ID does have to be unique. This enables us to do a joint on the employee's ID to the departments EMPL_ID. This inner joint will bring back employees, in my case, just one who are registered with a department. You indeed, it looks as if I'm iterating over the results set, getting data from both the employee and department table within each row that is returned. It's very important because of referential integrity, these foreign key, primary key relationships that you drop. The department table first has the foreign key and you don't drop the employee table first because it has the primary key that the foreign key is logging to. At the moment, we have an employee that could belong to multiple departments, but we can change that by just defining the foreign key being unique. EMPL_ID INT NOT NULL UNIQUE. For the definition of my foreign key. If I now try and load two departments that point to the same employee I1, which I did before, I will get an integrity constraint violation exception because it would mean that there are two rows on the department table that have the same foreign key. Even though the DEPT_ID, the primary key on the department table is always guaranteed to be unique.
