How to Create MYSQL Table with Foreign Key Constraint and add sample data

ERD:

ERD Credit from Group OnlineQuizPlatformSVFC

as you can see, we are successfully created the quiz_db

Step 1: MySQL Table Creation

Create the required tables using MySQL CREATE TABLE statements. Here is the SQL to create these tables:

1. User Table

CREATE TABLE User (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('teacher', 'student') NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
);

2. Quiz Table

CREATE TABLE Quiz (
    quiz_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    quiz_code VARCHAR(20) UNIQUE NOT NULL,
    teacher_id INT,
    duration INT NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES User(user_id)
);

3. Question Table

CREATE TABLE Question (
    question_id INT PRIMARY KEY AUTO_INCREMENT,
    quiz_id INT NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM('MCQ', 'True/False', 'Short Answer', 'Multimedia') NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id)
);

4. Option Table (For MCQ Questions)

CREATE TABLE OptionTable (
    option_id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT NOT NULL,
    option_text VARCHAR(255) NOT NULL,
    is_correct BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY (question_id) REFERENCES Question(question_id)
);

5. Answer Table

CREATE TABLE Answer (
    answer_id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT NOT NULL,
    student_id INT NOT NULL,
    answer_text TEXT,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES Question(question_id),
    FOREIGN KEY (student_id) REFERENCES User(user_id)
);

6. Result Table

CREATE TABLE Result (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    quiz_id INT NOT NULL,
    student_id INT NOT NULL,
    score DECIMAL(5, 2) NOT NULL,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id),
    FOREIGN KEY (student_id) REFERENCES User(user_id)
);

Adding Data with Foreign Key Rule

To add data with foreign key constraints, let's add some data into the User and Quiz tables, making sure the foreign key rules are respected.

-- Insert into User table
INSERT INTO User (name, email, password, role) VALUES 
('John Doe', '[email protected]', 'password123', 'teacher'),
('Jane Smith', '[email protected]', 'pass456', 'student');

-- Insert into Quiz table 
INSERT INTO Quiz (title, description, quiz_code, teacher_id, duration) VALUES
('Math Quiz', 'Basic Algebra', 'MATH101', 10, 60),
('Science Quiz', 'General Science', 'SCI201', 10S, 45);

TIP: we must first define the tables with relationships.

1. Add data on User Table:

INSERT INTO User (name, email, password, role) VALUES
('John Doe', '[email protected]', 'password123', 'teacher'),
('Jane Smith', '[email protected]', 'pass456', 'student'),
('Emily Clark', '[email protected]', 'teacherpass', 'teacher'),
('Michael Brown', '[email protected]', 'mike123', 'student'),
('Sarah White', '[email protected]', 'sarahpass', 'student');

2. Add data on Quiz Table:

INSERT INTO Quiz (title, description, quiz_code, teacher_id, duration) VALUES
('Math Quiz', 'Basic Algebra', 'MATH101', 1, 60),
('Science Quiz', 'General Science', 'SCI201', 1, 45),
('English Quiz', 'Grammar Basics', 'ENG301', 1, 30),
('History Quiz', 'World War II', 'HIST401', 8, 50),
('Physics Quiz', 'Mechanics', 'PHY501', 8, 40);

3. Add data on Question Table:

INSERT INTO Question (quiz_id, question_text, question_type) VALUES
(8, 'What is 2 + 2?', 'MCQ'),
(9, 'What is the chemical symbol for water?', 'MCQ'),
(10, 'Identify the verb in the sentence.', 'True/False'),
(11, 'When did World War II start?', 'Short Answer'),
(12, 'What is Newton’s second law?', 'MCQ');

4. Add data on Option Table (For MCQs):

INSERT INTO OptionTable (question_id, option_text, is_correct) VALUES
(6, '4', 1),
(6, '5', 0),
(7, 'H2O', 1),
(7, 'O2', 0),
(8, 'F = ma', 1);

5. Add data on Answer Table:

INSERT INTO Answer (question_id, student_id, answer_text) VALUES
(1, 2, '4'),
(2, 2, 'H2O'),
(3, 4, 'True'),
(4, 5, '1939'),
(5, 4, 'F = ma');

6.Add data on Result Table:

INSERT INTO Result (quiz_id, student_id, score) VALUES
(8, 2, 90.5),
(9, 2, 85.0),
(10, 9, 75.0),
(11, 10, 88.0),
(12, 9, 95.0);