question archive A primary key is used to identify a single instance of an entity
A primary key is used to identify a single instance of an entity
Subject:Computer SciencePrice: Bought3
A primary key is used to identify a single instance of an entity. This primary key only refers to one particular instance, and not any others. There cannot be repeat primary keys because then the key would be pointing to more than one instance. If we are creating a customer database, we need to be able to tell the difference between John Smith, who lives on 500 1st Street Pittsburgh, and John Smith who lives on 500 1st Street in Philadelphia. In this case, there are multiple John Smiths, and there are even multiple John Smiths that live on 500 1st Street. Here we would use a unique identifier, the primary key, to tell the difference between the two customers.
A natural key is a value that could be used as a primary key, but occurs naturally within the database. Using the above example, if we had a field called "social security number", this value would only point to one specific instance of a customer. Thus, it is a natural key. Again, following the above example, we could also issue a customer ID to each customer. This invented key would serve the same purpose, but was created specifically for identifying each customer, rather than being naturally occurring. The invented key has a disadvantage that it takes up more disc space, but has the advantage that the database could be easily updated in business rules were to change. Another advantage to invented keys is that they can accommodate special circumstances, such as when a customer does not have a SSN. An advantage of a natural key is that the key makes business sense, whereas an invented key only exists for the purpose of identifying an instance.
Foreign keys are created when instances in two tables share a relationship. They show how an instance in one table is related to an instance in another. Multiple foreign keys are allowed because by default, foreign keys can be multiple as well as null. If an instance of customers orders multiple instances of products, they are related through an order. The order number connects customer to product in the database.
