الأسبوع 5 · الوحدة ٢ «متطلبات ومراحل إنتاج المشروع» (٢) مرحلة إنشاء جداول قاعدة البيانات (١): قاعدة البيانات والجدول والسجل والحقل، أنواع الحقول وأحجامها، الجدول المسطح وقاعدة البيانات العلائقية، المفتاح الأساسي · 3 دروس
3
A value that never repeats: the primary key
ICT Secondary 2 Term 1, Unit 2, Topic 2: pp. 40–44
GoalExplain what a primary key is and choose one for a table.
New words
primary key · a field whose value is never repeated and never empty, so it identifies exactly one recordunique · different in every recordempty value · a field with nothing in it, which a key must never havemember number · an added field that gives every member his or her own number
Explanation
In a table we need a way to point to exactly one record. That is the job of the primary key: a field whose value is never repeated, even when the table grows huge, and never left empty. You can pick such a field from the existing ones, or add a new one. Try the field name in the members table. Two members could be called Mona, so the name may repeat, and it cannot be the key. The field level repeats many times too. The field team in a players table also repeats, because many players are on one team. None of these is safe. So we add a field, member_no, and give each member a different number. Then member_no 3 can only mean one person. A key must stay the same for the life of the record, which is another reason not to use a name that can change.
Primary key: member_no
member_no
name
level
books_read
1
Mona
silver
7
2
Omar
starter
2
3
Hana
gold
12
4
Karim
silver
6
5
Salma
starter
1
Worked examples
Field
Can it repeat?
Can it be empty?
Key?
member_no
no
no
yes
name
yes
no
no
level
yes
no
no
books_read
yes
no
no
Mona tests each field of the members table with two questions: can the value repeat? can it be empty? Only member_no passes both tests, so it is the key.
member_no
name
level
1
Mona
silver
2
Omar
starter
6
Mona
gold
Two members are both called Mona. If name were the key, “Mona” would not say which record is meant. With member_no the two Monas are records 1 and 6, and there is no doubt.
Class activity
Design a table on paper for the games club of your class, with at least four fields. Decide which field could be the primary key. If none can, add one and explain why each existing field fails the test.
Practice
1
State the two rules for a primary key.
Its value is never repeated and never empty.
2
Why is a team name not a good key in a players table?
Many players share one team, so the value repeats.
3
What can you do if no existing field is safe as a key?
Add a new field, such as a member number, with a different value for every record.
4
A key is never empty. Why would an empty key be a problem?
An empty value cannot point to any record, so the record could not be identified.