Page 1 of 1

SQL: An introduction to Keys Quick overview explaining how to use primary and foreign keys

#1 calebjonasson  Icon User is offline

  • $bert = new DragonUnicorn(); $bert->rawr();
  • member icon

Reputation: 207
  • View blog
  • Posts: 988
  • Joined: 28-February 09

Posted 23 May 2010 - 06:27 PM

The Primary Key

Cells inside of your database are all located through an X-axis and a Y-axis. The X-axis is a row and the Y-axis is the column. Because the information inside of a database is stored downward as you go through the table each added record needs a unique identifier to be found. This is where the primary key comes in.

For every table in your database a unique ID is required to find the records and because of this every table has exactly one Primary Key. Without a primary key there would be no way to reference the information stored inside of that row and data would become inaccessible very quickly. This is also the reason why the primary key cannot have the value of “null.”

In a table there can never two of the same key. Two keys would conflict with each other and return bad records, which is why it is impossible to have this. Generally when working on a database you never want to modify the key after it has been created. This would change how the data is accessed and would be a bad idea when it comes to referencing the information should anything go wrong. There are two different kinds of keys. The first kind is a simple key and which is made of one column. The second kind of key is a composite key, which is made of 2 or more columns.

In most cases the Primary key is an integer. This allows simple ordering and counting of records on system however there are cases where a hex number can be used or even a pattern of numbers and letters.

For example, say that we were creating a database for items in a store. Each item has a unique SKU but this is something can change over time so we would still need our primary key to be a number that is completely unique. The naming also will not work for a unique ID due to name changes or the same product name with a change in SKU, which happens over time with different versions and packaging.

Foreign Keys

Foreign keys are used to reference objects or rows from other tables. Theses keys generally reference parent tables from child tables and are used to cross data between tables. This means that the table with the foreign key is always going to be the child in the link.

Unlike the primary key, the foreign key can have a null value, which leads to no link being made between the two tables for that object. These keys also do not need to be unique; in fact they often never are unique do to the nature of how they are used.

An example of the foreign key, continuing from the example used in primary keys, will require us to add another column to the initial table so we will now have ID, SKU, and Vendor. The tables will look like this.

Posted Image

As you can see, in this example we have the foreign key doubling as a primary key in the lower table, which is used in as a vendor in the top table. This is a quick and simple way to store the values easily in the database and comes in handy when working with relationships.

Is This A Good Question/Topic? 4
  • +

Page 1 of 1