column_name data_type PRIMARY KEY AUTOINCREMENT
In order to create relational databases, you need to be able to identify each individual entity in a table, and the way we do this is through a PRIMARY KEY.
A table must have a primary key for each entity(row)
The primary key must be unique in that table.
For example, the primary key in the table below is the ID column:
Racers Table
NameAgeTeam
IDNameAgeTeam
1 | Bob Jones | 25 | Pirelli |
Each racer is given an ID number and that number is unique to the driver. This means that even if there are 2 driver, who are both 25 and both work for Pirelli, they won’t get confused by the database.
Challenge 17 – Create a new database
Database name – racing2.db
column_name DATA_TYPE REFERENCES table_name(primary_key_column_name)
So why have we created a primary key in the first place? It’s more effort than before? Well, the answer is that we can now use the primary in other tables to refer to that racer name. It connects the two tables together and allows us to do more clever things with the database than we did previously.
Flat File Database Example
Here is a simple flat file database. It looks pretty sound, but as you can see there is quite a bit of duplication between the two tables ( racerName, team). The more clever way of doing things would be to use a relational database setup instead, which would save duplication of data.
Relational Database Example
Not only does this save on duplication, but it also avoids storing some data altogether. For instance we can calculate how many times a racer has won a race by doing a clever JOIN lookup on both the racers and fixtures tables.
CREATE TABLE Pupil(id integer PRIMARY KEY,name text);
sqlite> CREATE TABLE Allergy(id integer PRIMARY KEY, name text, action text);
CREATE TABLE Pupil_Allergy(
id integer PRIMARY_key,
PupilID integer,
AllergyID integer,
FOREIGN KEY (PupilID) REFERENCES Pupil(id),
FOREIGN KEY (AllergyID) REFERENCES Allergy(id)
);
Challenge 18 - Racers
Challenge 18 – Racers
In your racing2.db database create the following table:
Table name
racers
Column names
- RacerId integer, auto incrementing
- FirstName text
- Lastname text
- Age integer
- TeamID foreign key integer
Challenge 19 - Fixtures
Challenge 19 – Fixtures
In your racing2.db database create the following table:
Table name
fixtures
Column names
- date
- time
- location
- name
Challenge 20 - Teams
Challenge 20 – Teams
In your racing2.db database create the following table:
Table name
teams
Column names
- name
- country
- manager
Challenge 21 - Results
Challenge 21 – Results
In your racing2.db database create the following table:
Table name
results
Column names
- fixture
- racer
- position
- time