Foreign key constraints, DB for real
Now that I am a happy user of PHP5 and MySQL5 and using that for writing a new application, which fortunately involves using the newest technologies, I am finally able to solve one of the biggest issues I always had had with DB-driven applications: foreign key contraints.
Constraints and transactions
I am using InnoDB (I know those contraints had been possible before MySQL5) for that and the first time I am also using the foreign key constraints InnoDB provides. And it does make me happier :-). I don’t have to take care of updating and deleting all the referenced table rows myself, the DB’s storage engine does that for me, as I always had wanted it.
I had started out using MyISAM tables and triggers, but when I realized that I am programming the application using transactions I had to switch to InnoDB for it to have any effect at all. Transactions solve the next problem I always had, uncompleted or failed database modifications.
I upgraded my mind too and was able to drop the triggers, doh. Oh man, that was a long process, even though all this knowledge was available already in some corner of my mind.
Watch out
But I actually wanted to write down the one thing that I had been looking for for a while:
Create foreign keys only on columns with the exact same type!
Of course. Yes, of course. Makes sense, but I just wanted to have a go and try the foreign key constraint and didn’t watch out for the types, then I got the error:
ERROR 1005: Can’t create table (errno: 150)
And the manual just told me the standard things, but not this!
Won’t work
CREATE TABLE chapter (
chapter_id int(10) NOT NULL auto_increment,
title varchar(20) NOT NULL,
PRIMARY KEY (chapter_id)
) ENGINE=InnoDB;
CREATE TABLE subchapter (
subchapter_id int(11) unsigned NOT NULL auto_increment,
chapter_id int(11) unsigned NOT NULL,
title varchar(20) NOT NULL,
PRIMARY KEY (subchapter_id),
KEY chapter_id (chapter_id),
FOREIGN KEY (chapter_id) REFERENCES chapter(chapter_id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
The two columns chapter_id are of a different type, int(10) and int(11), and one is signed the other unsigned! Only if you get both types exactly right the creation of the foreign key constraint will be successful! Otherwise you get the meaningless error message mentioned above, which does not really help.
Will work
CREATE TABLE chapter (
chapter_id int(11) unsigned NOT NULL auto_increment,
title varchar(20) NOT NULL,
PRIMARY KEY (chapter_id)
) ENGINE=InnoDB;
CREATE TABLE subchapter (
subchapter_id int(11) unsigned NOT NULL auto_increment,
chapter_id int(11) unsigned NOT NULL,
title varchar(20) NOT NULL,
PRIMARY KEY (subchapter_id),
KEY chapter_id (chapter_id),
FOREIGN KEY (chapter_id) REFERENCES chapter(chapter_id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
Let’s see what’s coming after InnoDB …
Ivo Jansch said,
April 17, 2006 at 5:43 pm
Some hints: don’t specify the type of a foreign key at all. It will automatically pick the type of the field it references. Also, I find it cleaner to give the constraints a name so they don’t get an autogenerated key name.
The ON UPDATE CASCADE is not really necessarry for numerical keys as you’re not going to change these ever.
Finally, I think it’s not necessary to have both a key and a foreign key on the same field. As a foreign key is a regular key as well.
Your example for subchapter would then become:
CREATE TABLE subchapter (
subchapter_id int(11) unsigned NOT NULL auto_increment,
chapter_id NOT NULL,
title varchar(20) NOT NULL,
PRIMARY KEY pk_subchapter (subchapter_id),,
FOREIGN KEY fk_subchapter_ch (chapter_id) REFERENCES chapter(chapter_id)
ON DELETE CASCADE
) ENGINE=InnoDB;
Note how chapter_id does not have a type here.
Wolfram said,
April 17, 2006 at 5:50 pm
ah yes, no type at all is even cooler and even more logical
thx
Wolfram said,
April 17, 2006 at 6:09 pm
Ivo, but your statement doesn’t work! It always raises:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘NOT NULL,
title varchar(20) NOT NULL,
PRIMARY KEY pk_subchapter (subchapter_id’ at line 3
seems you can’t leave out the type … even though it would make sense … what’s wrong?
Maarten Manders said,
April 17, 2006 at 6:17 pm
Isn’t InnnoDB a whole lot slower than MyISAM?
Ivo Jansch said,
April 17, 2006 at 6:27 pm
Hmm, you’re right, leaving out the type only seems to work in Oracle. I confused things
Lukas said,
April 17, 2006 at 6:36 pm
@Maarten: that depends on what you are doing .. for high concurrency between reads and writes InnoDB is faster due to row level locking and MVCC.
For reads only MyISAM is faster. It also does some aggregates faster because it does not have MVCC.
You have to look at what features you need and how you are interacting with your database to choose the proper table engine.
Jay Pipes said,
May 1, 2006 at 4:36 pm
Hi Wolfram!
Just to back up what Lukas was saying, performance of the storage engines depend very much on the application and its needs. InnoDB performance may be better for reads than MyISAM in some circumstances (high concurrency, for instance) and MyISAM can be better for writes in some circumstances (logging applications, for instance). The best advice is to build a benchmarking platform and test, test, test
Never take anything you hear for granted!
Cheers!
Official Green Bay Packers Jerseys said,
January 13, 2012 at 9:14 am
hello there and thank you to your information – I have definitely picked up anything new from right here. I did alternatively experience several technical issues using this web site, as I skilled to reload the website lots of occasions previous to I could get it to load correctly. I were considering if your hosting is OK? No longer that I’m complaining, but sluggish loading instances times will sometimes impact your placement in google and could injury your high quality ranking if advertising and ***********|advertising|advertising|advertising and *********** with Adwords. Well I’m adding this RSS to my email and could glance out for much extra of your respective interesting content. Ensure that you replace this again soon..
nahommkl said,
December 10, 2012 at 7:26 am
‘I couldn’t wait to shoot the water stuff, in the submarine,’ she recalls. canada goose jacket Honestly, I have no idea. canada goose outlet Cxoexgyru
windows 8 key Xsxfprbvw christian louboutin outlet online tnbnihyeo
sehkobpf said,
December 10, 2012 at 11:27 am
Men who are confident are always smiling. canada goose jackets Retailing from here on out is going to require turning a good part of this strategy upside down. hollister coats Gbzkrxpjq
windows 7 ultimate key Dzcgltzeh christian louboutin shoe sale yamiomxem