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
– Cthos, Commented Apr 23, 2011 at 0:19
@Oswald That's what I was thinking as well. Probably not InnoDB, if I had to guess offhand. — Cthos
– Cthos, Commented Apr 23, 2011 at 0:20
IN my create table I didn't declare the engine so it's using whatever the default engine in mysql is. — Justin Reagan
– Justin Reagan, Commented Apr 23, 2011 at 0:24
on the topic of SHOW CREATE TABLE, remember that code is king when asking for help. To create a minimal test case for DB related questions, include statements to create the table(s) and (if asking about DML 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
– outis, Commented Apr 23, 2011 at 0:27

Community · Accepted Answer · 2017-05-23 11:47:42Z

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

Collectives™ on Stack Overflow

Adding a constraint to MySQL table

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related