Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Jun 24, 2002

Introduction to Relational Databases - Page 2

By Ian Gilfillan


A key is the tool to unlock access to database tables. By knowing the key, we know how to locate specific records, and traverse the relationships between tables.

A candidate key is any field, or combination of fields, that uniquely identifies a record. The field/s of the candidate key must contain unique values (if the values were duplicated, they would be no longer identify unique records), and cannot contain a null value.

A primary key is the candidate key that has been chosen to identify unique records in a particular table.
Examine the following table:

Poet
CodeFirst NameSurnameAge
1MonganeAfrika62
2StephenSerote58
3TatumkhuluWatson29

At first it seems there are two candidate keys for this table. Both code and the combination of first_name and surname would suffice. It is always better to choose the candidate key with the least number of fields for the actual primary key, so we would choose code in this case. Also, if we thought about it some more, we'd soon realize that there is a possibility of the second combination not being unique. The combination of first_name and surname could be duplicated. So to consider this for a choice of primary key, we'd have to be sure that none of our poets could ever have the same name. This is the reason we assign code fields. Codes are assigned by our system, so we can ensure there are never any duplicate codes. After the primary key has been assigned, any remaining candidate keys are labeled alternate keys.

Foreign Keys

A relation between two tables is created by creating a common field to the two tables. The common field must be a primary key to the one table (the table that would be the one component of the one-to-many relationship). Consider a relation between a poet table and a poetry anthology table. The relation is of little use if instead of using the primary key from the poet table, code, to create the relationship with the anthology table, we use another field that is not unique, such as the poet's surname. We would never know for certain which poet we're referring to in the poetry anthology. The poet_code field is called the foreign key in the anthology table, which means it's the primary key(code) in the poet table.

Anthology
  • Anthology_code
  • Poet_code
Poet
  • Code
  • Firstname
  • Surname
  • Age

Foreign keys allow us to ensure what is called referential integrity. This means that if a foreign key contains a value, the value must refer to an existing record in the related table. For example, take a look at these two tables:

Poet
CodeFirstnameSurname
1MonganeAfrika
2StephenSerote
3TatumkhuluWatson

Poem
TitlePoet
Wakening Night1
Thrones of Darkness2
Once3

Referential integrity exists here, as all the poets listed in the poem table exist in the poet table. Stephen Serote now pulls out of the anthology, and we delete him from the poet table. In a situation where referential integrity is not enforced, he would not appear in the poet table, but the code he used to have would still appear in the poem table. So, when we look up the poet who wrote Thrones of Darkness (which is poet code 2) we are sent to a non-existent record.

Poet
CodeFirstnameSurname
1MonganeAfrika
3TatumkhuluWatson

Poem
TitlePoet
Wakening Night1
Thrones of Darkness2
Once3

The delete results in poor data integrity.

Foreign keys also allow what are called cascading deletes and updates. This means that we can delete Stephen Serote from the poet table, and all poems written by him, with one SQL statement. The delete "cascades" through the relevant tables, removing all relevant records. Foreign keys can contain null values if the relationship is optional, which indicates that no relationship exists. If the relationship is mandatory, the foreign key cannot contain nulls.


Page 3: Views




SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM