Re: PostgreSQL 8.1 vs. MySQL 5.0?

On Thu, Oct 06, 2005 at 12:35:38PM -0700, CSN wrote:
> Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> > But what really bugs me is that some things that ARE bugs simply aren't
> > getting fixed and probably won't. Specifically, while mysql understands
> > fk references made at a table level, it simply ignores, without error,
> > warning, or notice, fk references made in a column. arg... Very
> > frustrating. If they just didn't support that syntax it would be much
> > less bothersome, since I'd try it, get an error, and try the other
> > syntax. Instead, I spent an afternoon trying to figure out why it
> > wasn't doing ANYTHING when I declared an FK reference at column level.
>
> What's the difference between a fk at the table level
> vs. column level? The only fk's I've used are one
> column referencing another.

He means the way the foreign key constraint is defined. In MySQL,
defining the constraint as part of column definition has no effect:

CREATE TABLE bar (
fooid integer NOT NULL REFERENCES foo (id)
) TYPE innodb;

The database accepts the above without warning but won't enforce
the foreign key constraint. One must write this instead:

CREATE TABLE bar (
fooid integer NOT NULL,
FOREIGN KEY (fooid) REFERENCES foo (id)
) TYPE innodb;

Also, notice the "TYPE innodb" clause of the CREATE TABLE statement.
The default table type in MySQL is MyISAM, which doesn't support
foreign key contraints at all, but which will silently allow you
to declare them. If you haven't changed the default table type,
then you must remember to specify that you want an InnoDB table,
or else your REFERENCES clauses are nothing but documentation.

--
Michael Fuhr