* Andy Dustman > On Mon, 2002-06-24 at 17:50, Roger Baklund wrote: > > * [EMAIL PROTECTED] > > [...] > > > The referred to keys are the same type and are indexed (they are > > [...] > > > CREATE TABLE genres ( > > > genre_id SMALLINT NOT NULL PRIMARY KEY, > > [...] > > > genre_id SMALLINT, > > > > ...does not look the same to me... ;) > > Ouch, you are correct, although they are the same *type*. However, this > seems like a fairly severe restriction. Obviously a primary key must be > NOT NULL, but there are certainly cases where a column which refers to a > foreign key must be NULL. The docs do say that the foreign key > constraints *can* have an ON DELETE SET NULL clause, which is > meaningless if your column has to be set to NOT NULL due to referring to > a primary key.
Yes, you are right, as Heikki pointed out in the previous message in this thread. > Perhaps this is only a deficiency, and not a bug, but the documentation > could be a bit clearer on this. It says, "Corresponding columns in the > foreign key and the referenced key must have similar internal data types > inside InnoDB so that they can be compared without a type conversion." > My understanding is that NOT NULL is only a column constraint and > doesn't affect the data type. > > However... Even when I set the referring column to be SMALLINT NOT NULL, > and create a new database, I still get the same error: > > ERROR 1005 at line 23: Can't create table './MP3d/tracks.frm' (errno: > 150) Like Heikki said: your problem is probably that you don't have indexes on the foreign keys. -- Roger sql --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
