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

ERD:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1728660669973/1233aef0-0b71-4d95-a45c-acb38872fcd8.png align="left")

[ERD Credit from Group OnlineQuizPlatformSVFC](https://github.com/OnlineQuizPlatformSVFC/OnlineQuizPlatform_Documentation/blob/main/OnlineQuizPlatform_Docu.md)

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877062514/9ce4dd41-b372-4989-82a8-45b8afff0573.png align="center")

  

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">SHOW DATABASES</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729876591577/274399f2-6390-465c-ab00-71c84924518c.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">CREATE quiz_db database</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729876632107/ac7e2a05-30d9-45e0-8a2c-9d193be42cb4.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Show database again</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729876699604/952fb87d-dde7-4660-a5a5-1054c2927c4c.png align="center")

as you can see, we are successfully created the quiz\_db  

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">use quiz_db;</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729876732547/b92bf4a3-486d-494e-8942-821a4c694ac6.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">SHOW TABLES;</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729876757752/fe98e132-b23e-4f02-9594-be33aea26109.png align="center")

---

### 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**

```typescript
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
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877254324/890f34df-6759-41a2-a343-1273c73daa46.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">we can show the tables and describe it</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877368039/943116b8-cc8e-47be-b7d4-280bcfcc1fa0.png align="center")

---

### 2\. **Quiz Table**

```typescript
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)
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877497592/b8e65d67-7839-48ae-b66b-4f39641ac2d9.png align="center")

---

### 3\. **Question Table**

```typescript
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)
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877614929/5695bbf3-f497-45b7-8873-edec9a17d239.png align="center")

---

### 4\. **Option Table** (For MCQ Questions)

```typescript
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)
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877770287/50b179e5-6835-4a89-ba12-5ad1739f2cd6.png align="center")

---

### 5\. **Answer Table**

```typescript
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)
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729877969882/ed6525ae-a874-4876-86a0-95e08ea203b7.png align="center")

---

### 6\. **Result Table**

```typescript
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)
);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729878035219/c778f5fa-aa50-4089-acc8-740241b4e07a.png align="center")

---

### 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.

```sql
-- Insert into User table
INSERT INTO User (name, email, password, role) VALUES 
('John Doe', 'john.doe@example.com', 'password123', 'teacher'),
('Jane Smith', 'jane.smith@example.com', '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);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729878324621/e1dd9c6c-603e-4f67-b08c-2b311585405f.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">In this example we received an error cause a <code>Quiz</code> table that references a <code>User</code> table via <code>teacher_id</code>, the value in <code>teacher_id</code> must correspond to an existing <code>user_id</code> in the <code>User</code> table. This helps maintain consistency and prevent invalid data from being entered.</div>
</div>

---

### TIP: we must first define the **tables** with relationships.

### 1\. Add data on **User Table**:

```sql
INSERT INTO User (name, email, password, role) VALUES
('John Doe', 'john.doe@example.com', 'password123', 'teacher'),
('Jane Smith', 'jane.smith@example.com', 'pass456', 'student'),
('Emily Clark', 'emily.clark@example.com', 'teacherpass', 'teacher'),
('Michael Brown', 'michael.brown@example.com', 'mike123', 'student'),
('Sarah White', 'sarah.white@example.com', 'sarahpass', 'student');
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729878744679/7dde13d8-750a-4bdb-b5d0-ba64b117b5e9.png align="center")

### 2\. Add data on **Quiz Table**:

```sql
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);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729878826887/5f389284-4eb5-4de3-8a59-b0c2c05afcc2.png align="center")

### 3\. Add data on **Question Table**:

```sql
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');
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729878918309/b8eebe32-b157-4da2-b707-f20f728cbfca.png align="center")

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

```sql
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**:

```sql
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');
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729878985554/c743bfe8-85cb-488d-b89a-2077c4d387e9.png align="center")

### 6.Add data on **Result Table**:

```sql
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);
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729879086301/1b92399a-a8f0-40e5-a95f-19866f6d7079.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Foreign keys maintain <strong>data integrity</strong> by ensuring relationships between records. For example in this article, quizzes cannot exist without a valid teacher, and quiz results must reference existing quizzes and students. This structure prevents invalid data from entering your database, helping keep the system <strong>reliable and consistent</strong>.</div>
</div>