Introduction[]
Primary and foreign keys play an important role in the existence of the relational database. It is the ability to link tables with keys that allows for the logical representation of information relational databases provide. Each table has a primary key that uniquely identifies tuples in that table. By including a primary key from one table in another a foreign key is implemented, joining the two tables, creating a relationship.
Key Types[]
Definition of a Key - One or more attributes that determine other attributes.
Role - based on the concept of Determination.
Determination - If you can determine attribute 'B' by knowing attribute 'A', then you can say that “A determines B”. If it takes more information than 'A' to determine 'B', then it is true that "A partially determines B". For a primary key-attribute relationship to exist, it must be true that all attributes involved are fully functionally dependent on the key.
There are 3 rules that apply to primary keys:
· Exist (i.e., not be null)
· Be unique within the context
· Not change over time (unique sustained existence)
In relational model notation the primary key is first, underlined and bold.
Employee (Emp_ID, Emp_Fname, Lname, Phone )
Types of Keys in a Relational Database[]
1. Superkey - A set of attributes that uniquely identifies each tuple in a relation
2. Candidate Key - Similar to a superkey, but does not contain a subset of attributes that is itself a superkey. In other words, a "minimal superkey".
3. Primary Key (PK) - Candidate Key selected to uniquely identify all other attribute values in any given entity. A primary key can be made with multiple attributes and cannot contain NULL values.
"Every table must have a primary key, an attribute or combination of attributes that are guaranteed to be unique and not null.
The entity integrity rule states that for every instance of an entity, the value of the primary key must exist, be unique, and cannot be null."
4. Secondary Key - An attribute or combination of attributes that identify a set of rows based on a value. Generally used strictly for data retrieval purposes.
5. Foreign Key (FK) - An attribute or combination of attributes in one table whose values must either match the primary key (PK) in another table or be NULL.
"A foreign key exists in a table to identify a primary key in another. The join of two relations is made at the foreign key and with
the referential integrity rule applied creates reliable navigation of relations and data integrity.
The referential integrity rule states that every foreign key value must match a primary key value in an associated table.
When designing and establishing relationships, the cardinality needs to be considered. Always use the primary key from the 1 side of
the relationship to be the foreign key in the N side."[[File:]]