Primary Key Characteristics
A primary key is an attribute that uniquely identifies the entity that it resides in.For a primary key to be useful and functional there are several characteristics that should be followed.They are as follows:
Unique values – The primary key must not be null because it uniquely identifies the table.
Nonintelligent -The primary key should be “fact less” meaning that it cannot be composed semantic data.For example in an entity called STUDENT_INFO, school_ID composed of numbers would be a better choice for a primary key than first_name or last_name.
No Change Over Time – When picking a primary key it is wise to avoid semantic data because it could be subject to change.If primary keys are changed then the foreign keys must be updated as well.Since primary keys are the identity of the table or entity, it should be permanent and unchangeable
Single-Attribute – The primary key should be composed of only one attribute, however this is not required.If the primary key is a composite primary key ( one made up of multiple attribute), it will cause the primary keys of other entities to have multiple attributes as well.
Preferable Numeric – Primary keys are easier and better managed when they are composed of mostly numeric data.This is useful because when new data is being entered, the database MS can employ a counter style attribute where with each new entry, the database program generates a number then increments the number by one automatically for the next entry.This function is built-in to most systems, for example Access has an Auto-number construct.
Security Complaint – The selected primary must not be an attribute that is consideredsensitive information for example it would not be a good idea to set a social security numberof a person as a primary key
Types of Keys
Composite Primary Key – a primary key that is made up of more than one attribute.
Surrogate Primary Key – a system assigned primary key generally numeric and auto-incremented
Natural Key – a real world, generally accepted identifier used to distinguish real world objects
Candidate Key- a minimal superkey that does not contain a subset of attributes that it itself a superkey
Foreign Key – an attribute in one table whose values must match the primary key in another table
This category currently contains no pages or media.