Primary key & Composite key

11.4.1.3 explain the difference between primary composite and foreign key

Primary key & Composite key. Foreign key

A primary key is a field in a table that contains unique data.

A primary is a single column value used to identify a database record uniquely.

It has following attributes

  • A primary key cannot be NULL
  • A primary key value must be unique
  • The primary key values should rarely be changed
  • The primary key must be given a value when a new record is inserted.

Let's look at an example in the Criminal Table:

  • Table: Criminal 
    • Attributes:
    • NI Number: String
    • Name: String
    • Date of Birth: Date
    • Number of scars: Integer
    • Home town: String

Which of these data items are unique?

Attribute Unique Reason
Home_town No you might have several criminals living in the same town
Number_of_scars No you might have two criminals with the same number of scars
Date_of_Birth No you might have two criminals born on the same day, or twins who are criminals
Name No you might have two criminals with the same name, e.g. John Smith and John Smith
IDnumber Yes this is unique for each person

There is a short cut to writing out database table designs, where the underlined attribute is the primary key. Note that primary keys are normally written first.

TableName(PrimaryKey, Attribute, Attribute, Attribute, Attribute)

Criminal(IDnumber, Name, Date_of_Birth, Number_of_scars, Home_town)

Composite key - сollection of attributes uniquely identify a tuple rather than just one.
However, things may not always be so clearly cut. Take this a table of football players for example:

  • Table:Player
    • Name: string
    • Position: string
    • Number: integer
    • Injured: boolean
    • Team: String

Which of these would be the primary key?
 

Attribute Unique Reason
Name No you might have several players with the same name
Position No you might have two goalies
Number No you might be storing the details of multiple teams, in which case each number has several players from different teams
Injured No several people might be injured at the same time
Team No several players can play for the same team

 So what do we do? Before we make our database tables it's best to go through this process and we come to realise that we need to introduce another attribute that is unique for each player. We might even invent one, a playerID:

Player(PlayerID, Name, Position, Number, Injured, Team) 

Another way of solving these problems is to try and find a combination of attributes that together are unique. This is called a composite key. Take a look at this example for houses:

  • Table: House
    • Number: integer
    • Road: string
    • Colour: string
    • Post code: string
       
Attribute  Unique Reason
Number No you might have a 61 on London Rd and a 61 on Manchester Rd
Road No you might have multiple houses on the same road
Colour No more than one house might be green
post code No multiple houses might have the same post code

We could make a primary key up, but if you look carefully, we can use a combination of attributes. The house number and the road name combined seem to be unique. You can't have two 45 Belmont Close can you?.

House(Number, Road, Colour, Post code) 

Does that sound ok? What about if we were storing data on all the towns in the country and there was a 5 London Road in Manchester and a 5 London Road in Winchester. This would mean that the combination was not unique. We might try using the house number and post code instead, and this combination is always unique, this is our composite key:

House(Number, Road, Colour, Post code

Foreign key is a field in one table that is linked to the primary key in another table.

 

Категория: Relational databases | Добавил: bzfar77 (04.11.2020)
Просмотров: 104 | Теги: composite key, database, primary key | Рейтинг: 5.0/1
Всего комментариев: 0
avatar