databasedev.co.uk - database solutions and downloads for microsoft access

Designer for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available

Primary and Foreign key constraints

Primary and Foreign key constraints are and what they are used for:


Primary Key:

A primary key is a field or combination of fields that uniquely identify a record in a table, so that an individual record can be located without confusion.

Foreign Key:

A foreign key (sometimes called a referencing key) is a key used to link two tables together. Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key (it remains a primary key in the original table).

More complicated but fuller explanation:

Employee Table
EmployeeID (PK) FirstName LastName Department Manager
001 Stan Smithers IT Support Stan Smithers
002 Joe Bloggs Sales Joe Bloggs
003 Mark Richards Sales Joe Bloggs
004 Jenny Lane Marketing Jenny Lane
005 Sally Holmes Sales Joe Bloggs
006 John Lee IT Support Stan Smithers

A primary key is the field(s) (a primary key can be made up of more than one field) that uniquely identifies each record, i.e. the primary key is unique for each record and the value is never duplicated in the same table, so in the above table the EmployeeID field would be used. A constraint is a rule that defines what data is valid for a given field. So a primary key constraint is a rule that says that the primary key fields cannot be null and cannot contain duplicate data.

The problem with the above table is that you have repeating information in the manager field, this causes all sorts of problems, e.g. Fred Bloggs leaves and Jenny Smith becomes sales manager, you now have to replace all entries that say Fred Bloggs with Jenny Smith.

If however you split the last two fields out to make a department table you would only need one entry for each department, when a manager changes you only need to make the change in one place, if you setup a primary key of DeptID in the department table you would have the following.

Department Table
DeptID (PK) Department Manager
01 IT Support Stan Smithers
02 Sales Joe Bloggs
03 Marketing Jenny Lane
Employee Table
EmployeeID (PK) FirstName LastName
001 Stan Smithers
002 Joe Bloggs
003 Mark Richards
004 Jenny Lane
005 Sally Holmes
006 John Lee

You now need to link the two table together so you know which department each employee is in, so what you do is take the primary key from the department table and insert it into the employee table (where it becomes a foreign key as a foreign key is the primary key from one table inserted into another table to link them).

Employee Table
EmployeeID (PK) FirstName LastName DeptID (FK)
001 Stan Smithers 01
002 Joe Bloggs 02
003 Mark Richards 02
004 Jenny Lane 03
005 Sally Holmes 02
006 John Lee 01

A foreign key constraint specifies that the data in a foreign key must match the data in the primary key of the linked table, in the above example we couldn't set the DeptID in the Employee table to 04 as there is no DeptID of 04 in the Department table. This system is called referential integrity, it is to ensure that the data entered is correct and not orphaned (i.e. there are no broken links between data in the tables)

The other added advantage is that you are saving space, if the following were the field sizes for the tables we have:

  • EmployeeID = 3 characters
  • Firstname = 10 characters
  • Surname = 10 characters
  • Department = 10 characters
  • DeptID = 2 characters
  • Manager = 20 characters

The original Employee Table would take 53 characters per record, 6 records gives us 318 characters.

The latest version of the Employee Table would take 25 characters, 6 records gives us 150 characters. The Department table would take 32 characters and there a 3 records so 96 characters, so 150+96 = 246 characters.

So over a very simple structure with just 6 records we have saved ourselves 72 characters, which would be 72 Bytes.

Doesn't sound much on 6 records but if we had 600 employees the original system would take 53*600 = 31800 characters. Whereas the new system would take 25*600 = 15000 + 32*3 = 96

Which is a total of 15096 characters, a saving of 16704 characters so we have saved over 50% of the storage space.