Re: PostgreSQL 8.1 vs. MySQL 5.0?

On Thursday 06 October 2005 17:31, Michael Fuhr wrote:
> On Thu, Oct 06, 2005 at 12:35:38PM -0700, CSN wrote:
> > Scott Marlowe <smarlowe@g2switchworks.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.

I'm working on porting mediawiki to postgresql and was really puzzled by the
following:

CREATE TABLE trackbacks (
        tb_id           INTEGER AUTO_INCREMENT PRIMARY KEY,
        tb_page         INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
        tb_title        VARCHAR(255) NOT NULL,
        tb_url          VARCHAR(255) NOT NULL,
        tb_ex           TEXT,
        tb_name         VARCHAR(255),

        INDEX (tb_page)
);

I couldn't figure out why they weren't specifying type = innodb for the table,
but then figured they must have declared it some place else or something...
but now I see that even that wouldn't work. Makes you wonder if my$ql users
realize this behavior or not....I would have to guess not because otherwise
why would you use this type of syntax at all?

(And people claim my$ql is eaiser to use? I still don't get that one)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL