hateful hates software: MySQL and foreign-key support

• From: Aaron Crane
  Date: 13:16 on 19 Oct 2006
  Subject: MySQL and foreign-key support
  
  (This started out as a polite response on another mailing list to someone
  saying "You guys are out of date, MySQL is a pretty decent db now".  But it
  got a bit ranty, so I thought I'd share it with all you software haters
  instead.  Enjoy!)
  
  Current releases of MySQL certainly have many of the checklist features of a
  real DBMS.  However, this does not constitute a defensible claim that MySQL
  is a real DBMS.
  
  MySQL still, after all these years, has a whole host of arbitrary
  limitations (like the limits on key length, and the inability to use TEXT
  columns in a foreign key, and the lack of recursion in stored functions).
  And there are still important features missing, whatever table engine you
  use (like CHECK constraints, and deferred constraint checking).
  
  Worst of all, though, MySQL seems to deliberately make it hard to use the
  features it does have.  A recent project needed transactions and foreign-key
  references in a MySQL 5.0 database (the current stable release, please
  note), so we used InnoDB tables.  The situation was broadly equivalent to
  this:
  
    CREATE TABLE actor (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name TEXT NOT NULL
    ) ENGINE=InnoDB;
  
    CREATE TABLE film (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name TEXT NOT NULL
    ) ENGINE=InnoDB;
  
    CREATE TABLE film_actor (
      film_id INT NOT NULL REFERENCES film,
      actor_id INT NOT NULL REFERENCES actor,
      PRIMARY KEY (film_id, actor_id)
    ) ENGINE=InnoDB;
  
  Simple enough, yes?
  
  No, actually.  As it happens, using REFERENCES as a column constraint
  doesn't work at all, and you also have to explicitly specify the primary-key
  columns of the referenced table.  So the only permitted syntax for foreign
  keys is this (which you'll notice is much more verbose than the obvious
  version):
  
    CREATE TABLE film_actor (
      film_id INT NOT NULL,
      actor_id INT NOT NULL,
      PRIMARY KEY (film_id, actor_id),
      FOREIGN KEY (film_id) REFERENCES film (id),
      FOREIGN KEY (actor_id) REFERENCES actor (id)
    ) ENGINE=InnoDB;
  
  So guess what happens when you use one of the naughty-but-convenient
  syntaxes?
  
  That's right -- MySQL just silently ignores what you said!  No error, no
  warning, just a blithe "sure, no problem mate, I'll get right on it",
  without actually doing what you asked.
  
  Why, yes, this _did_ cost me a day of debugging, thanks for asking.
  
  I'm aware of MySQL's propensity for handling compatibility requirements by
  "helpfully" ignoring any bits of syntax that the engine doesn't have the
  semantics for.  But what made me so fucking angry about this was that the
  engine _does_ have the semantics for this!  How hard could it be to just
  do the right thing?
  
  Hate.
  
  
  

• From: A. Pagaltzis
  Date: 18:31 on 19 Oct 2006
  Subject: Re: MySQL and foreign-key support
  
  * Aaron Crane <hateful@xxxxxxxxxx.xx.xx> [2006-10-19 14:20]:
  > MySQL still, after all these years, has a whole host of
  > arbitrary limitations (like the limits on key length, and the
  > inability to use TEXT columns in a foreign key, and the lack of
  > recursion in stored functions). And there are still important
  > features missing, whatever table engine you use (like CHECK
  > constraints, and deferred constraint checking).
  
  Not only those, but it will also still silently coerce data into
  compliance with constraints, like silently truncating values on
  INSERT and successfully performing the insertion instead of
  rejecting the query.
  
  "NO, YOU PIECE OF EXCREMENT, THESE WERE *NOT* SUPPOSED TO BE
  ACCEPTED!" (Four days of checking records and cleaning up the
  data ensue. Arrrrgh. *writhe*)
  
  In compassionate hate,
  
  

• From: David Cantrell
  Date: 19:08 on 23 Oct 2006
  Subject: Re: MySQL and foreign-key support
  
  On Thu, Oct 19, 2006 at 07:31:02PM +0200, A. Pagaltzis wrote:
  > * Aaron Crane <hateful@xxxxxxxxxx.xx.xx> [2006-10-19 14:20]:
  > > MySQL still, after all these years, has a whole host of
  > > arbitrary limitations (like the limits on key length, and the
  > > inability to use TEXT columns in a foreign key, and the lack of
  > > recursion in stored functions). And there are still important
  > > features missing, whatever table engine you use (like CHECK
  > > constraints, and deferred constraint checking).
  > Not only those, but it will also still silently coerce data into
  > compliance with constraints, like silently truncating values on
  > INSERT and successfully performing the insertion instead of
  > rejecting the query.
  
  Of course, other databases also hatefully truncate data on INSERT too.
  It's probably even required by some stupid standard.
  
  
  

• From: Philip Newton
  Date: 19:16 on 23 Oct 2006
  Subject: Re: MySQL and foreign-key support
  
  On 10/23/06, David Cantrell <david@xxxxxxxx.xxx.xx> wrote:
  > On Thu, Oct 19, 2006 at 07:31:02PM +0200, A. Pagaltzis wrote:
  > > Not only those, but it will also still silently coerce data into
  > > compliance with constraints, like silently truncating values on
  > > INSERT and successfully performing the insertion instead of
  > > rejecting the query.
  >
  > Of course, other databases also hatefully truncate data on INSERT too.
  > It's probably even required by some stupid standard.
  
  And let's not even get started with databases who won't let you insert
  empty strings into VARCHAR columns, turning them into NULL instead.
  
  Somebody has grossly misunderstood what "NULL" means, methinks.
  
  

• From: Peter da Silva
  Date: 01:33 on 24 Oct 2006
  Subject: Re: MySQL and foreign-key support
  
  On Oct 23, 2006, at 1:16 PM, Philip Newton wrote:
  > And let's not even get started with databases who won't let you insert
  > empty strings into VARCHAR columns, turning them into NULL instead.
  
  Sure that's the DB and not some hateful access library that's covering 
  for a shortcoming in the language runtime?
  
  A lot of them want you to specify the string to use as "null value".
  
  Just make sure you hate the right software. You can get out of practice 
  focussing your hate if you spread it about too much.
  
  
  

• From: Philip Newton
  Date: 06:14 on 24 Oct 2006
  Subject: Re: MySQL and foreign-key support
  
  On 10/24/06, Peter da Silva <peter@xxxxxxx.xxx> wrote:
  > On Oct 23, 2006, at 1:16 PM, Philip Newton wrote:
  > > And let's not even get started with databases who won't let you insert
  > > empty strings into VARCHAR columns, turning them into NULL instead.
  >
  > Sure that's the DB and not some hateful access library that's covering
  > for a shortcoming in the language runtime?
  >
  > A lot of them want you to specify the string to use as "null value".
  
  Pretty sure. It does it even in the supplied interactive SQL console,
  and a couple of Google hits imply that it's a "feature" that's been
  part of that particular engine for quite a while.
  
  

• From: David King
  Date: 18:35 on 19 Oct 2006
  Subject: Re: MySQL and foreign-key support
  
  > MySQL still, after all these years, has a whole host of arbitrary
  > limitations (like the limits on key length, and the inability to  
  > use TEXT
  > columns in a foreign key, and the lack of recursion in stored  
  > functions).
  > And there are still important features missing, whatever table  
  > engine you
  > use (like CHECK constraints, and deferred constraint checking).
  
  Indeed, try something like this:
  
  select foo from (
  	select foo,bar from baz
  	limit 5
  ) as quux;
  
  "Limit not allowed in subselect".
  
  Postgres seems to handle it fine.
  
  select id from ( select id from products limit 1) as foo;
  --
  1
  
  This is useful in limiting results based on something other than the  
  total number of rows (for instance, limiting the number of categories  
  returned without limiting the total number of rows)
  
  
  
  
  

Generated at 10:26 on 16 Apr 2008 by mariachi