Be able to create and edit primary and foreign keys

18. Databases – Primary & Foreign Keys

🔑 What is a Primary Key?

A primary key ( PK ) is a column or set of columns that uniquely identifies each row in a table. Think of it as a student’s unique ID number – no two students can have the same ID. This guarantees that every record can be found without confusion.

🛠️ Creating a Primary Key

1. Choose the column(s) that will uniquely identify the data.
2. Define the column as a primary key when you create the table:

CREATE TABLE Students (
    StudentID INT NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    PRIMARY KEY (StudentID)
);

✏️ Editing a Primary Key

If you need to change the primary key (e.g., you decide to use a combination of StudentID and Year), you can drop the old key and add a new one:

ALTER TABLE Students
DROP PRIMARY KEY,
ADD PRIMARY KEY (StudentID, Year);

🔗 What is a Foreign Key?

A foreign key ( FK ) is a column that creates a link between two tables. It ensures that the value in one table matches a value in another table, maintaining data integrity. Imagine a student’s record pointing to the course they are enrolled in – the course ID must exist in the Courses table.

📚 Example: Students & Courses

🛠️ Creating a Foreign Key

CREATE TABLE Courses (
    CourseID VARCHAR(10) NOT NULL,
    CourseName VARCHAR(100),
    PRIMARY KEY (CourseID)
);

CREATE TABLE Enrollments (
    EnrollmentID INT NOT NULL,
    StudentID INT,
    CourseID VARCHAR(10),
    PRIMARY KEY (EnrollmentID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

✏️ Editing a Foreign Key

To modify a foreign key, you first drop it and then add the new constraint. For example, if you want to change the referenced column:

ALTER TABLE Enrollments
DROP FOREIGN KEY FK_Enrollments_CourseID,
ADD CONSTRAINT FK_Enrollments_CourseID
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID);

💡 Key Takeaways

• Primary keys uniquely identify rows – think of them as a student’s ID.
• Foreign keys link tables – they enforce that a value exists in another table.
• Use PRIMARY KEY and FOREIGN KEY clauses when creating tables.
• Alter tables with ALTER TABLE to add, drop, or modify keys.
• Maintaining keys keeps your database consistent and error‑free.