Using the Foreign Key
You always need a foreign key in the many table when you have a 1:M relationship. The database must include this relationship somewhere. A nonrelational (object-oriented) database might store the relationship as pointers, or addresses in a one table record that point to associated records in the many table.
However, in a relational database management system (RDBMS), pointers aren't necessary and might not be used to define the 1:M relationships. Instead, when you specify two related tables in a JOIN query, the values in the foreign key column of the many table are compared to the values in the primary key column of the one table. If the two values match, the query returns that record in the result set.
Although I've been talking about a foreign key and its corresponding primary key, a foreign key can also be compared to a candidate key. In the April issue, I talked about what criteria you use to promote a candidate to primary key. A candidate key can be a primary key, but for business reasons, which you determine, it isn't always. Such is the case in the Employee table, where both EmpNo and EmpSSN are candidates, but for privacy reasons, I've promoted EmpNo to primary key.
Make sure that a foreign key and its corresponding primary or candidate key have the same data type and length when you create tables. (You don't have to make the attribute names the same.) Then you can use the following query, for example, to compare the values of EmpSSN in the Employee table to the values of EmpSSN in the Paycheck table and generate a report listing the total amount of pay each employee has received. The report appears in Table 1.
SELECT EmpLname, EmpFname, SUM(PayAmt) AS
"Total Pay Week 15-17"
FROM Employee INNER JOIN PayCheck ON
Employee.EmpSSN = Paycheck.EmpSSN
GROUP BY EmpLname, EmpFname
You can use EmpNo to do the comparison in the previous query and generate the same result set. However, a reasonable assumption is that the payroll department will reference employees by Social Security Number (SSN).
In much the same way, by comparing ProjNo from the Project table to ProjNo from the Employee table, you can use the following query to generate a report that lists employees and their associated projects. The output report appears in Table 2.
SELECT ProjName, EmpFname + " " + EmpLname
AS "Project Team Member"
FROM Project LEFT OUTER JOIN Employee ON
Project.ProjNo = Employee.ProjNo
ORDER BY ProjName
The SQL Server query optimizer uses an index on the foreign key column, if one is available, to resolve a JOIN query if the tables are large (multipage). (For more information on the SQL Server query processor, see Petkovic and Unterreitmeier, "New Features for Query Processing," July 1999.)
When you create the foreign key reference, SQL Server doesn't automatically build an index for the foreign key column. Create the foreign key index in a separate step:
CREATE INDEX idx_PaycheckEmpNo ON
paycheck(EmpNo)
After you declare the foreign key relationship to the DBMS, the DBMS always enforces it; you cannot program around the constraint. For example, if you try to insert a record containing an employee ID of 6 into the Paycheck table and no employee number 6 exists, the DBMS will reject your INSERT query. If you try to delete the record for Becky Brown in the Employee table, the DBMS will reject that operation with a warning: DELETE statement conflicted with COLUMN REFERENCE constraint 'fk_Paycheck2Emp.' The conflict occurred in database 'SQLmag,' table 'Employee,' column 'EmpNo.' This message means that if you want to get rid of Becky Brown, you first have to remove from the Paycheck table all records that refer to her. Then you can remove her employee record from the Employee table. DRI, or the foreign key constraint, prevents you from leaving behind orphan records for Becky Brown in the Paycheck table.
Value-Added Design
A foreign key creates a bridge between two tables in a database. Database programmers use the foreign key as one of the critical components to dynamically join data from two or more tables. Without a foreign key, if you want to create a report that uses data from more than one table, you have to resort to exhaustive coding in a non-SQL programming language.
Extracting information from multiple tables is significantly complicated beyond the simple joins shown earlier, and end-user access to the data diminishes as the complexity of retrieval increases. You might be tempted to denormalize the tables to make data access easier for the end user. But table denormalization compromises data integrity because you have to manually synchronize duplicate data (the result of denormalization) on each data update.
The foreign key and the primary key control updates to a database. Used correctly, the foreign key can ensure database table integrity.
End of Article
Prev. page
1
[2]
next page -->
