Database Keys. Primary Keys and Foreign Keys

As you may already know, databases use tables to organize information. Each table consists of a number of rows, each of which corresponds to a single database record. So, how do databases keep all of these records straight? It’s through the use of keys.
Primary Keys

The first type of key we’ll discuss is the primary key. Every database table should have one or more columns designated as the primary key. The value this key holds should be unique for each record in the database. For example, assume we have a table called Employees that contains personnel information for every employee in our firm. We’d need to select an appropriate primary key that would uniquely identify each employee. Your first thought might be to use the employee’s name.

This wouldn’t work out very well because it’s conceivable that you’d hire two employees with the same name. A better choice might be to use a unique employee ID number that you assign to each employee when they’re hired. Some organizations choose to use Social Security Numbers (or similar government identifiers) for this task because each employee already has one and they’re guaranteed to be unique. However, the use of Social Security Numbers for this purpose is highly controversial due to privacy concerns. (If you work for a government organization, the use of a Social Security Number may even be illegal under the Privacy Act of 1974.) For this reason, most organizations have shifted to the use of unique identifiers (employee ID, student ID, etc.) that don’t share these privacy concerns.

Once you decide upon a primary key and set it up in the database, the database management system will enforce the uniqueness of the key. If you try to insert a record into a table with a primary key that duplicates an existing record, the insert will fail.

Most databases are also capable of generating their own primary keys. Microsoft Access, for example, may be configured to use the AutoNumber data type to assign a unique ID to each record in the table. While effective, this is a bad design practice because it leaves you with a meaningless value in each record in the table. Why not use that space to store something useful?
Foreign Keys

The other type of key that we’ll discuss in this course is the foreign key. These keys are used to create relationships between tables. Natural relationships exist between tables in most database structures. Returning to our employees database, let’s imagine that we wanted to add a table containing departmental information to the database. This new table might be called Departments and would contain a large amount of information about the department as a whole. We’d also want to include information about the employees in the department, but it would be redundant to have the same information in two tables (Employees and Departments). Instead, we can create a relationship between the two tables.

Let’s assume that the Departments table uses the Department Name column as the primary key. To create a relationship between the two tables, we add a new column to the Employees table called Department. We then fill in the name of the department to which each employee belongs. We also inform the database management system that the Department column in the Employees table is a foreign key that references the Departments table. The database will then enforce referential integrity by ensuring that all of the values in the Departments column of the Employees table have corresponding entries in the Departments table.

Note that there is no uniqueness constraint for a foreign key. We may (and most likely do!) have more than one employee belonging to a single department. Similarly, there’s no requirement that an entry in the Departments table have any corresponding entry in the Employees table. It is possible that we’d have a department with no employees.

Adding to text fields together in Microsoft Access

I have a very short post for you guys today but it can save you lots of time.  I needed to add a person's last, first, and middle names together in one field for a report.  Each one of these is a separate field in my database.  I accomplished this by creating a query and adding all the appropriate fields that I needed.  Next, I clicked in a blank field in the query designer and added the following line of code. 

Full_Name: [Name_Last] & "" & ", " & "" & [Name_First_MI] & "" & " " & "" & [Name_Middle]


This concatenated all these fields together with a comma and a space after the last name and a space after the first name.   After that it was easy to create the report based on this query.  It looks great and I have adapted this technique to other data types as well. 

I hope this will be useful to you in the future. 

Internet Explorer 9 Beta

Today in San Francisco, Microsoft will officially unveil Internet Explorer 9 and make it available to the general public. It is, without question, the most ambitious browser release Microsoft has ever undertaken, and despite the beta label it is an impressively polished product.

For the record, I haven’t used Internet Explorer in years.  I usually stick with Safari on my Mac and Firefox on my PC.  However, with the release of IE9 I might have to change my default browser back to IE. 

The new IE has a greatly improved javascript engine and it renders HTML much better than previous versions.  It is also embracing HTML5 which is something IE has needed to do for sometime now. 

The biggest difference to me is the UI.  It is very minimal and there is almost no branding beyond the logo on the task bar.  This browser focuses more on the content of the web page than on the browser. 

At any rate, you should check out the beta version for yourself and give it an honest try.  It may win you back to the Internet Explorer users group!

Keyboard Shortcut for Access 2007

I have a small tip for you Microsoft Access people out there.  If you want to make a keyboard shortcut for a button click event or a shortcut for some type of an event in a form, I have a simple way to accomplish this. 

Use the “&” sign followed by the key you want to use as the shortcut after the caption definition and there you have it.  For example, if you have a button with a caption called Close you can create the shortcut by typing the “&C” after the word close.  After you have completed that you should notice the letter C on the button is underlined.  This means typing the letter c on your keyboard will activate the button.  Now you have a keyboard shortcut.  Have fun and don’t forget to comment!

If you have any topic ideas, or you have an article that you want to submit for posting on this blog, email me at jeff.trehern@gmail.com

Thanks!

Jeff