HelpContactFor teachers and institutes
>_StemPy Lab
Mock TestsClasses

>_StemPy Lab

Learn. Solve. Code the solutions. Test yourself.

Live examsFree mock exams, timed and rankedEveryone starts together, and your score and rank come the moment it ends.See the exams →

Learn

  • Physics
  • Chemistry
  • Mathematics
  • Biology
  • Python Programming
  • Statistics & Data Science
  • Computer Science
  • Classes
  • Groups

Practice

  • Problems
  • Quizzes
  • Mock Tests
  • Prepare for an Exam
  • Question Sets
  • Exams
  • Certificates
  • Python Labs
  • AI Practice Generator
  • Student-Created Tests

Community

  • Discussions
  • Resources
  • Notes
  • Glossary
  • Blog

Account

  • Log in
© 2026 senLearn. All rights reserved.HelpFor SchoolsAboutContactPrivacy PolicyTerms of UseRefund Policy
Database Tables, Data Types and Primary Keys
  1. 1Tables, fields and records
  2. 2Data types and the primary key
  3. 3Validation in a database table
  1. Computer Science
  2. /Database Tables, Data Types and Primary Keys
  3. /Data types and the primary key

Course 9.1·Lesson 2 of 3

Data types and the primary key

Each field stores one kind of data, so each field gets a data type: text, character, Boolean, integer, real or date/time. Each table also needs a primary key, a field whose value is different in every record. You choose suitable data types and pick a suitable primary key for a table.

about 25 min1 simulation1 practice question
Lesson 2 of 3Show all lessonsHide
Database Tables, Data Types and Primary Keys
  1. 1Tables, fields and records
  2. 2Data types and the primary key
  3. 3Validation in a database table

Try the code and the simulations beside the lesson.

What kind of data is it?

Look at a bus ticket. It has the route name, the price, the date and the time. A price is a number that you can add up. A route name is words. A date is a date. A database stores each field with a data type that suits it.

The six data types

The syllabus names six basic data types.

  • Text (also called alphanumeric): letters, digits and symbols. A name, a town, a code such as M01 or a phone number.
  • Character: exactly one letter, digit or symbol. A grade such as B, or a size such as M.
  • Boolean: one of two values, TRUE or FALSE. Has the member paid? Is the book on loan?
  • Integer: a whole number. An age, a number of goals, the number of items in stock.
  • Real: a number with a decimal part. A price such as 3.753.753.75, a mass, a height in metres.
  • Date/time: a date, a time, or both. A date of birth, the start time of a match.
Six labelled cards, one for each data type, each with two example values: Text or alphanumeric (Asha, M01), Character (B, M), Boolean (TRUE, FALSE), Integer (15, 0), Real (3.75, 1.62) and Date/time (2024-03-14, 10:30).

For the Members table of the last lesson: MemberID, FirstName, Surname and Town are text, Age is an integer and Paid is a Boolean.

Choosing the data type

Ask two questions about each field. What do the values look like? What will we do with them?

  • A phone number is text, not an integer. It can start with 000 or with a plus sign, and nobody adds two phone numbers together. An integer field would lose the first 000.
  • A code such as M01 is text, because it has a letter in it.
  • A price is real, because it has a decimal part and we add prices up.
  • A yes or no question is Boolean. Do not store "yes" as text: a Boolean has only two values, so nobody can type "yse" by mistake.
  • A single letter is a character, for example a shirt size S, M or L.

In SQLite. The database inside Python names the types TEXT, INTEGER and REAL. It stores a Boolean as 1 for TRUE and 0 for FALSE, and it stores a date as text such as 2024-03-14. Written year first, dates sort in the right order. An examination paper may write a date in another form, such as 14/03/2024.

The primary key

How does the club tell its two members called Patel apart? It uses MemberID. No two members have the same ID.

A primary key is a field whose value is unique: different in every record. It identifies exactly one record. It must never be left empty.

To pick a primary key, look down each field and ask: could two records ever have the same value here?

  • Surname: no good. Asha and Ravi are both Patel.
  • Town and Age: no good. Riverside and 151515 appear twice.
  • FirstName: no repeats today, but a second Tom could join next week.
  • MemberID: made to be unique. This is the primary key.
Three columns of the Members table side by side: Surname with Patel shown twice and marked 'repeats: not a key', Town with Riverside and Hillview each shown twice and marked 'repeats: not a key', and MemberID with M01 to M05 all different and marked 'unique: primary key'.

Often no natural field is unique, so we add an ID field, as the vet did with PetID. A field such as a car's registration number is already unique, so it can be the key of a table of cars.

Practise with new numbers

Try it in Python

The program builds a small table of cars with Registration as the primary key. Then it tries to add a car with a registration that is already in the table. SQLite refuses it, because a primary key must be unique.

Read it first. Then run it. Which car is refused, and why?

1. Change the registration of the new car to "FT22 HJK". Predict what happens, then run it.

2. How many cars are in the table at the end of each run?

Common mistakes

  • A phone number as an integer. It loses its first 000. Store it as text.
  • Real for a whole number. An age or a number of goals is an integer.
  • Text for a yes or no answer. Use a Boolean.
  • A name as the primary key. Two people can share a name. A key must be unique in every record, now and later.
  • Choosing a key from today's data only. A field with no repeats today may get one tomorrow.

Remember these

  • The six data types: text or alphanumeric, character, Boolean, integer, real, and date/time.
  • Choose a type from what the values look like and what you will do with them.
  • A primary key is a field with a unique value in every record. It identifies one record.

Check yourself

Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.

  1. Which data type suits a field ShoeSize that holds values such as 5.55.55.5 and 777?
  1. Which data type suits a field HasPet?
  1. A field Postcode holds values such as RV2 7QT. Which data type suits it?
  1. A table of students has the fields FirstName, Surname, DateOfBirth and StudentNumber. Which is the most suitable primary key?
  1. Why should a primary key never be left empty?

References

  • Cambridge International, IGCSE Computer Science 0478 syllabus (exams in 2026, 2027 and 2028), section 9 Databases, 9.2 (suitable basic data types) and 9.3 (the purpose of a primary key).

Enroll free in Database Tables, Data Types and Primary Keys

Every lesson is open to read. Enrolling also lets you:

  • Mark lessons done and carry on where you stopped.
  • Take the tests of this lesson and its course.
  • Count the course towards the certificate for its chapter.
No payment. You can leave at any time.

Problems (4)

A sports day table has these fields. Suggest a suitable data type for each one…Easy · High School (9–10) · by StemPy Lab
A garden centre stores its plants in this table…Medium · High School (9–10) · by StemPy Lab
A shop gives every product a code: the letter P followed by a fixed number of digits, such as P0427…Hard · High School (9–10) · by StemPy Lab
Next: lesson 3Validation in a database table
← Database Tables, Data Types and Primary Keys
← Tables, fields and recordsValidation in a database table →

Discussion (0)

Log in to join the discussion.

Loading…

1 Python program
5 questions to check yourself

This lesson is free to read. Enroll free to keep your place and take the tests.

A student designs a table of club members and stores the phone number 07700 900814 in a field Phone…Medium · High School (9–10) · by StemPy Lab

Python Labs (1)

Build a table with a primary keyPython Lab · by StemPy Lab