It’s a beautiful Sunday afternoon here in San Diego. Wish l could be on the beach but l’m just home chilling. Ok, l’m going to continue the tutorial on database development. Today, l’ll be talking about Keys. I wanted to talk about Entity Relationship Diagrams and Relation Schema today but l think that’s just going to be too much to chew at a time so l’ll break the tutorial up into different components for each of these topics so we can better digest the material.
What is a key? It’s just a column or columns that uniquely identify a record in a table. At the entity level, we have Attribute keys, which can be described as an attribute that uniquely identifies an entity. There are different type of keys that can exist in the database. We’ll start discussing all these keys as follows:
Candidate Key: This is a set consisting of columns that can uniquely identify a record in a table. There is no sql state to define columns or keys as candidate keys. It’s a logical idea and has no statement for declaration. So, if there are many columns that can uniquely identify the same table in the database, all those columns form a set called the candidate key. For example, if you have a table called person and there are columns like phone, email, name etc. Then you recognise based on the business rule that, each customer has only one phone and that is unique, each customer has only one email and it’s also unique. Secondly, you recognise both email and cell phone can be used to uniquely identify a customer, then a set consisting of email and phone is called Candidate key.
Primary Key: Primary key is part of the Candidate key set. It is the column chosen from the candidate key as the one to use to uniquely identify records in the table at the physical level. Any key in the candidate key could be a chosen as the candidate key but the decision to choose a column as a primary key is totally up to the programmer and performance requirements. There are rules that must be obeyed on primary keys: primary key can’t not be NULL in a table. That means, it must always be given a value. A NULL doesn’t mean blank. In fact, a blank is a value. A NULL simply means unknown value. Second, a primary key can not have duplicate values in the same table. Another important thing about a primary key is that, creating it will create a clustered index on the table automatically. Clustered index is a storage technique used by the database for faster searching and query execution. It refers to the storage order of the records in the table. In short, the database will try to store the records in the table in order based on the key used as the primary key, That is a record whose primary key is say 1 will come first then follow by a record with primary key 2. This can be alphabetical order or grouping order like first name and last name. Take it like how info is stored in order in a phone book.
Unique Key: Unique key and Primary are very similar with just very little differences. A unique key uniquely identifies records in a table and it’s main purpose is to guide data integrity. That is to avoid bad data being entered into columns that are supposed to be unique but are not the declared primary key of the table. So, let’s say you have a table and declared a primary key for it on a column but there are other columns in the table that you want to protect against bad data such as not having duplicates just like a primary key. You then declare those columns as Unique keys. In short, a table can have only one primary key, but you can declare other columns as unique to behave almost like a primary key. I said almost like a primary key because unique keys can have a NULL value but each Unique key can have only one NULL in the table because there can be no duplicates for the Unique key. If you give the Unique key on the second time, it will give you error because the second NULL will result in duplicates. The biggest difference between a Unique key and Primary key is that, a Unique Key can take one NULL but primary key doesn’t take NULL. Another difference is that, creating a Unique key creates non clustered index on the column used for the Unique key. There can exist only one clustered index per table but there can be multiple non clustered index per table for up to 999, I think. I discussed clustered index earlier so let me give a short at non clustered index. It’s a logical idea and its used purpose for efficiency. It results in faster queries and searches. The idea of a non clustered index is that, the database will create another table internally that stores the data in the order of the column that has the non clustered index constraint. This doesn’t have anything to do with the original table. The idea is, when a query is executed and it has something to do with columns that have the non clustered index constraint, then those tables created by the database can be used to execute the query instead of the original table for faster performance. This concept of clustered and non clustered is related to Microsoft sql server or TSQL as far as l know. I have not done research on other DBMS whether they have the same concept. As l said in the beginning of the tutorial, l’m writing straight out of my brain with no books or resources based on my studies when l was in school, so l leave further research to readers to be totally confident and hence perfectly understand this tutorial.
Alternate Key: as l said earlier that candidate key has no statement to declare them, there is no statement to declare alternate key either. An alternate key is simply any other key that can identify records in a table after the primary key has been chosen from the candidate key set. This is the simplest definition l can think of. This is a logical idea too. there is no implementation for it in the table.
Secondary key: This is also a logical idea with no implementation in the table. Secondary key just means the next in line after the primary key is chosen from the candidate key set that can be used as primary key. Here is a funny analogy, imagine the primary key dies, the next person to be the chief from this royal family(candidate keys) is logically called the secondary key lol.
Composite Key: This is the last key l’m going to talk about. A composite key is just any key type that has multiple columns forming the same key. For example, you can have composite primary key. This means, there are more than one column used for this key. For example, lets assume we have a table called persons and for some strange reason, our business rule states that no two people can have the same full name( first name last name) and you as the programmer, decided to use the first name and last name columns together to declare your primary key. Then you have a composite primary key. Remember, in this case, you are talking of the two columns together and everything else apply. That means, no duplicate full name and no NULL for full name. There can be duplicate first names and last names separately but you can not have duplicate full name or NULL full name. l hope l make this a little clearer.
Foreign Key : This is a column in a table that points to the primary key column of another table. The purpose of the Foreign key is to provide referential integrity. Referential integrity simply means restricting the values that can exist in a table in regard to those that exist in another table. Simply, you want to make sure that all the values that can be inserted in the column of the foreign key exists in the primary key column of the other table. This is how a relationship is maintained between these tables.
We’ll see how all these are implemented in the coming tutorials.
I’m going to write on Entity Relationship Diagram in toturial 4, so check it out. We’ll be drawing ER diagrams for sample businesses.
Until then, Peace.
