How to enforce constraints in mysql

 > CREATE TABLE student(
-> student_id INT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> first_name VARCHAR(10),
-> last_name VARCHAR(10)
-> );

 > CREATE TABLE course(
-> course_id CHAR(5) NOT NULL PRIMARY KEY,
-> course_name VARCHAR(50),
-> student_id INT(2) NOT NULL,
-> CONSTRAINT student_student_id_fk
-> FOREIGN KEY (student_id)
-> REFERENCES student(student_id)
-> );

Thats how i created two tables namely student and course. Then i entered data in the student table. But when i enter some invalid data in the course table, it doesn't gives me any error. For example: VALUES('A1','SUB 1',34); gets the entry in the course table even if t开发者_开发百科here is no primary key '34' in the student table.

Also, i can delete records in the student table, even if there is referential integrity. So, how can enforce the constraints?

The easiest way to do this is to change your storage engine to InnoDB which supports the constraints.

For old MyISAM tables, you will have to use triggers on BOTH sides to enforce an FK relationship

Some links for self-help

• http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html
• http://forge.mysql.com/wiki/Triggers

Create your tables like this

 > CREATE TABLE student(
-> student_id INT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> first_name VARCHAR(10),
-> last_name VARCHAR(10)
-> ) ENGINE = INNODB;

 > CREATE TABLE course(
-> course_id CHAR(5) NOT NULL PRIMARY KEY,
-> course_name VARCHAR(50),
-> student_id INT(2) NOT NULL,
-> CONSTRAINT student_student_id_fk
-> FOREIGN KEY (student_id)
-> REFERENCES student(student_id)
-> ) ENGINE = INNODB;

For someone having the same problem, if you already created your table using MyISAM, you can change it to InnoDB using.

ALTER TABLE table_name ENGINE=InnoDB;