StandardNerd StandardNerd - 6 months ago 14
PHP Question

php database table creation fails

I use the following gist to make an OOP attempt to create a database connection:

https://gist.github.com/jonashansen229/4534794

It seems to work so far.

But the creation of the database table passed_exams fails.

Edit:

After recent comments and suggestions i updated my code:

require_once 'Database.php'; // the gist 4534794

class DatabaseSchema {

public function createStudents() {
$db = Database::getInstance();
$mysqli = $db->getConnection();
$create_students = 'CREATE TABLE IF NOT EXISTS students (
id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
university VARCHAR(50)
)';
$result = $mysqli->query($create_students);
}

public function createPassedExams() {
$db = Database::getInstance();
$mysqli = $db->getConnection();
$create_passed_exams = 'CREATE TABLE IF NOT EXISTS passed_exams (
id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
student_id INT(6),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
)';
$result = $mysqli->query($create_passed_exams);
}

}

$db_student = new DatabaseSchema();

$db_student->createStudents();
$db_student->createPassedExams();


When i look in the mysql console, only table students is created.
Why is table passed_exams missing?

Answer

The id column on your students table is INT(6) UNSIGNED but the student_id column on the passed_exams table is a signed INT(6). Therefore the FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE clause will fail with "Error Code: 1215. Cannot add foreign key constraint".

I advise you to implement some error handling so that you would see this error message rather than blindly continue executing code.

Comments