To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_KEYS.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>



SQL - Keys

Objectives

The objective of the lesson is to describe the keys and present examples that show how to use them.

Introduction

In the lesson on SQL DISTINCT, we saw a database pictured as:

 
+------------+    +-----------------+ 
|  Employee  |    |Employee_Pay     |
+------------+    +-----------------+
| emp_ID     |--->| emp_ID          |
| lastName   |    | position        |
| firstName  |    | dateOfHire      |
| middleName |    | payRate         |
| address    |    | dateOfLastRaise |
| city       |    | bonus           |
| state      |    +-----------------+
| zip        |
| phone      |
| cellPhone  |
| emailAddr  |
+------------+


+------------+    +-------------------+    +--------------+ 
|  Customers |    |    Orders         |    |  Inventory   |
+------------+    +-------------------+    +--------------+
| cust_ID    |-+  | orderNum          | +->| prod_ID      |
| lastName   | +->| cust_ID           | |  | partNum      |
| firstName  |    | prod_ID           |-+  | typeOfTool   |  
| middleName |    | quantity          |    | Brand        | 
| address    |    | orderDate         |    | prodDesc     | 
| city       |    | emp_ID            |    | inStock      |
| zip        |    +-------------------+    | price        |
| phone      |                             +--------------+ 
| cellPhone  |
| emailAddr  |
| typeCust   |
+------------+

where we assumed that we owned a chain of hardware stores. The five tables pictured above constitute our relational database. In the Employee table, the emp_ID field is called the primary key. The same field in the Employee_Pay table is called a foreign key. This is interesting. What makes a field a primary key in one table and a foreign key in another table? We will describe the difference in the next section.

Primary Key

The purpose of a primary key in a relational table is to uniquely identify each record in the table. A primary key may consist of one or more columns. The fundamental goals of a primary key should be:
  • Each record in a table should have, at least, one field/column that is unique within the table
  • For a record to be unique, data in, at least, one column should not be repeated anywhere else in the table.
    • This means that there exists no other record within the table that has all of its columns equal to any other.
    • The purpose of emp_ID in the Employee above table is to provide a unique field/column.
    • The primary key should be defined such that it is a unique value, such as a Social Security Number or a non-duplicated whole number.
    • Examples of fields that can be unique are:
      • Social Security Number
      • Employee ID
      • Credit Card Number
      • Checking Account number within a bank
      • Driver's License within a state

Foreign Key

A foreign key is a column or a combination of columns in a child table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the child table where that column is the primary key. In terms of DBMS jargon, this correspondence is known as referential integrity.

 
+------------+    +-----------------+ 
|  Employee  |    |Employee_Pay     |
+------------+    +-----------------+
| emp_ID     |--->| emp_ID          |
| lastName   |    | position        |
| firstName  |    | dateOfHire      |
| middleName |    | payRate         |
| address    |    | dateOfLastRaise |
| city       |    | bonus           |
| state      |    +-----------------+
| zip        |
| phone      |
| cellPhone  |
| emailAddr  |
+------------+
In this above example, the field emp_ID exists in both the Employee and the Employee_Pay tables. In the Employee table, emp_ID is the primary key, and in the Employee_Pay table, it is the foreign key. The foreign key is used to establish and enforce a link between the data in two tables. The link is created between two tables by adding the column or columns that hold one table's primary key values to the child table.

Summary

  • The primary keys and foreign keys are a very important part of relational databases. They are the fields that relate tables to each other.
    • Each record in a table should have, at least, one field/column that is unique within the table
    • For a record to be unique, data in, at least, one column should not be repeated anywhere else in the table.
  • A foreign key is a column or a combination of columns in a child table where that column is a primary key of another table,












[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works