Multiple choiceQuestion 1 · Recall
Loading…
By the end you can use ALTER TABLE to add or remove a field and to add keys, and you can
explain what a foreign key enforces and why the referenced table has to exist first.
You build a Student table, load 400 rows, and then a teacher asks for everyone's email
address. The design was not wrong — it was just incomplete.
You have two options. Delete everything and rebuild, or alter the definition in place.
Rebuilding is slow, loses data and needs everyone to stop working. So SQL gives you
ALTER TABLE, which changes the structure of a table that is already full of rows.
It is still DDL: it changes a definition, not the data. The DBMS updates the data dictionary to match, so the schema and the programs agree afterwards.
ALTER TABLE Student ADD Email VARCHAR(60);
ALTER TABLE Student DROP Email;
ADD puts a new field at the end of the table. Every existing row gets an empty
(NULL) value in it, because the data was never collected.DROP removes the field and every value stored in it. The other fields are
untouched.You can add several fields at once with a comma-separated list:
ALTER TABLE Student ADD Email VARCHAR(60), Guardian VARCHAR(50);
Notice the pattern: ALTER TABLE + table name + ADD/DROP + field definition (or just
the field name when dropping).
Worked example: Worked example: adding then removing a field
A school's Student table has StudentID, FirstName, Average and Enrolled. The school
starts collecting mobile numbers, then decides to stop storing them. Write both statements.
Sometimes a table was created without its keys — perhaps the data was imported from a
spreadsheet. You can add them with ALTER TABLE:
ALTER TABLE Student ADD PRIMARY KEY (StudentID);
ALTER TABLE Student ADD FOREIGN KEY (ClassID) REFERENCES Class (ClassID);
Both use the same ADD, and both put the field name in brackets after the rule.
A foreign key is a field in one table whose values must match the primary key of another table. It is how two tables are linked:
Class is the parent table: ClassID is its primary key.Student is the child table: its ClassID says which class that student is in.REFERENCES Class (ClassID) tells the DBMS to check every Student.ClassID against the
values in Class.ClassID.Without the foreign key, nothing stops a clerk typing ClassID = 4 for a student when there
is no class 4. The row is saved, and the student is now in a class that does not exist. Later
reports quietly leave them out, and nobody notices for a term.
With the foreign key, the DBMS checks the reference and rejects that row immediately. This is referential integrity: every reference points at something real.
The rule cuts both ways:
Analogy: a hotel key card. The card only works for a room that exists. If the room is taken out of service, the card stops opening it. Where it breaks down: a hotel can renumber rooms and relabel cards; a database would need the key values updated in every child row.
Because the DBMS checks that the referenced field exists, you must create the parent table before the child's foreign key:
CREATE TABLE Class (...) with ClassID as its primary key.CREATE TABLE Student (...) with a ClassID field.CREATE TABLE or later with ALTER TABLE.The referenced field must be a primary key in the parent (in Cambridge questions it always is). If you try to reference a field that does not exist, or reference it before the parent table exists, the DBMS rejects the statement with an error.