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
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.
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:
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.
DeptID (PK) | Department | Manager |
---|---|---|
01 | IT Support | Stan Smithers |
02 | Sales | Joe Bloggs |
03 | Marketing | Jenny Lane |
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).
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:
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.