Compare and contrast:
-- schema A
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
vendor_id INT UNSIGNED REFERENCES vendors (id),
name VARCHAR(255),
UNIQUE ( name ),
INDEX ( vendor_id )
) TYPE = InnoDB;
-- schema B
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
vendor_id INT UNSIGNED,
name VARCHAR(255),
UNIQUE ( name ),
INDEX ( vendor_id ),
FOREIGN KEY ( vendor_id ) REFERENCES vendors ( id )
) TYPE = InnoDB;
Schema B sets up the meta-data that Rose::DB::Object::Loader uses to figure out the relationships between tables automatically. Schema A does not.
Six.... fardling... hours.... wasted.
Many thanks to John Siracusa for pointing me to the solution.
You can almost see the bit of code that needs to be refactored.
Yeah, I've already seen the annoying bit about inline FK references being parsed and discarded. Further, I was pretty sure it was silently discarded, so I ran your query:
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
vendor_id INT UNSIGNED REFERENCES vendors (id),
name VARCHAR(255),
UNIQUE ( name ),
INDEX ( vendor_id )
) TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------- -----------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------- ----------------+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+----------------------------------------------------------- ---------------+
1 row in set (0.00 sec)
Grr
Drop the table, s/TYPE/ENGINE/, rerun create statement. No warnings.
MySQL sucks. I need to just get that printed on a damned t-shirt and wear it everywhere.
Re:FK Constraints
sigzero on 2007-03-29T11:55:29
Maybe it can be a can of tuna with a dolphin on it and the label says "MySQL Sucks!".
I have seen enough just on these blogs that I would have to agree.Re:FK Constraints
perrin on 2007-03-29T20:15:55
Does the incorrect syntax generate an error if you run MySQL in strict mode?Re:FK Constraints
Ovid on 2007-03-29T20:37:49
Our version of MySQL is old enough that strict mode is not available. I guess that makes my post a wee bit unfair
:) Re:FK Constraints
Aristotle on 2007-04-01T01:30:38
Quoth Aaron Crane on hates-software:
Ah, yes, the “version N+1” problem: everything you think you might want out of MySQL is supported (somehow, even if brokenly and hatefully) in the version after the one that’s available on your production servers. (Actually, sometimes MySQL makes it version N+k, to give you some light relief.) I believe the “version N+1” phrasing is due to Smylers (the original poster in this thread), circa 2000. It’s still true. Seven years later. Still true.