rho's Journal: Another MySQL Interlude

A comparison between the "easy to use" MySQL and the "hard to use" PostgreSQL:

MySQL:

CREATE TABLE up_subjects_books (
BOOK_ID int(11),
SUBJECT_ID int(11),
KEY bo_id (BOOK_ID),
KEY su_id (SUBJECT_ID),
CONSTRAINT up_bo_fk1 FOREIGN KEY (BOOK_ID) REFERENCES up_books (id) ON DELETE RESTRICT,
CONSTRAINT up_sub_fk1 FOREIGN KEY (SUBJECT_ID) REFERENCES up_subjects (id) ON DELETE RESTRICT
) ENGINE=InnoDB;

PostgreSQL:

CREATE TABLE up_subjects_books (
BOOK_ID INTEGER REFERENCES up_books(id),
SUBJECT_ID INTEGER REFERENCES up_subjects(id)
);

Of course, many people don't bother with foreign key constraints and they do such things programmatically. Which, to my mind, makes nothing easier. It simply makes the programmer responsible for data integrity, which is a recipe for trouble.

If there's a better way to do this in MySQL I'd love to hear it. I can't find it in the official documentation, but to be fair I stopped looking when I finally found an incantation that fucking worked. "INTEGER REFERENCES up_books(id)" certainly works, but it doesn't establish a constraint, which makes the whole exercise silly in my opinion. Why on Earth would anybody pick MySQL? I certainly wouldn't, but the server where this Web app currently sits is dropping its Oracle license and they've only installed MySQL.

(Oracle has its own set of oddities and quirks which annoy me, but at least I had confidence in the engine. To my mind, PostgreSQL is so far superior to both MySQL and Oracle for small-to-large database projects it's not even funny. Easy, fast and predictable. For certain enterprise setups I can see where Oracle stomps all over the free software options, and if Oracle were more reasonably priced I wouldn't be opposed to using it. But you have to be a sadist to want to do something non-trivial in MySQL.)

The reason we don't bother in mySQL

[TheConfusedOne]

Is that mySQL has a habit of ignoring foreign key constraints, especially if you're using MyISAM. :-D

Being a programmer first and a DB administrator/can-make-it-run and the desire to remain portable on the DB side pushes me to implement most functions in code instead of the DB as they might be different going from mySQL to either Oracle or SQLServer (shudder).

Re:

[rho]

Okay, I can understand that rationale. I guess my complaint is mostly relevant because I tend to write most applications from scratch, depending on PHP for the interesting presentation but keeping the data as normalized as I can make it in the database. If I venture much beyond from-scratch applications I tend to lean on pre-written packages, and in those cases I don't care much about the database backend except how to back it up.