Adding a constraint to MySQL table

Question

I have 2 tables one named Students and one named Grades and I'm trying to add a foreign key constraint to the Grades table so that if someone is deleted from the Students table all of their grades will be deleted as well. I have a column named TNumber to match the 2. Here's my code so far.

    ALTER TABLE Grades
    ADD CONSTRAINT fk_Grades
    FOREIGN KEY (TNumber)
    REFERENCES Students(TNumber) ON DELETE CASCADE;

The problem is that the code runs but it doesn't create the foreign key. Could someone just look at it and see if I'm doing something wrong in my syntax because the code runs and doesn't throw any errors.

Would you mind giving us a SHOW CREATE TABLE of both of your tables? — Cthos, Apr 23 '11 at 00:19
@Oswald That's what I was thinking as well. Probably not InnoDB, if I had to guess offhand. — Cthos, Apr 23 '11 at 00:20
IN my create table I didn't declare the engine so it's using whatever the default engine in mysql is. — Justin Reagan, Apr 23 '11 at 00:24
on the topic of `SHOW CREATE TABLE`, remember that [code is king](http://tinyurl.com/so-hints) when asking for help. To create a [minimal test case](http://sscce.org/) for DB related questions, include statements to create the table(s) and (if asking about [DML](http://en.wikipedia.org/wiki/Data_Manipulation_Language) queries) fill them with sample data (`INSERT INTO ...`). For a question such as this, where the problem doesn't depend on the data in the tables, you can leave out the statements to fill the tables with sample data. — outis, Apr 23 '11 at 00:27

score 4 · Accepted Answer · edited May 23 '17 at 11:47

The most likely reason (and the one hinted at in the comments) given that the statement completes without error but seems to have no effect is that one or both of the tables use the MyISAM engine, which is usually the default. Both tables need to use an engine that supports foreign keys, such as InnoDB.

If your tables are MyISAM tables, you can convert them to InnoDB tables with the following:

ALTER TABLE Students Engine=InnoDB;
ALTER TABLE Grades Engine=InnoDB;

After that, try adding the foreign key again. A heads up: the columns in the foreign key constraint need to have the same type. If there are differences, you'll get the extremely unhelpful "ERROR 1005 (HY000): Can't create table filename (errno: 150)" error message.

Note that using the InnoDB engine has consequences (see also "Storage Engine Performance Benchmark for MyISAM and InnoDB" and whatever a web search may turn up) beyond allowing foreign key restraints, but most of them are beneficial.

In such a case is there an exception thrown when you try and add it? — Conrad Frix, Apr 23 '11 at 00:24
that is the problem the tables were using MyISAM my professor told us to create the tables he never even said to specify what engine. — Justin Reagan, Apr 23 '11 at 00:29

Adding a constraint to MySQL table

1 Answers1