Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

rjbs (4671)

  (email not shown publicly)
AOL IM: RicardoJBSignes (Add Buddy, Send Message)
Yahoo! ID: RicardoSignes (Add User, Send Message)

I'm a Perl coder living in Bethlehem, PA and working Philadelphia. I'm a philosopher and theologan by training, but I was shocked to learn upon my graduation that these skills don't have many associated careers. Now I write code.

Journal of rjbs (4671)

Thursday June 21, 2007
07:31 AM

more mysql things that make you go hmmm

[ #33570 ]

I know that there's a MySQL 5 out. I do. It's just that upgrading a big system takes time, and there usually has to be some pressing reason. I've just found one more reason to upgrade sooner: as far as I can tell, MySQL 4 can't have fields that are not null and have no default.

In a real relational database, I'd say:

CREATE TABLE events ( id integer PRIMARY KEY SERIAL, event_time datetime NOT NULL DEFAULT current_time, event varchar(128) NOT NULL, );

Then, you could:

INSERT INTO events (event) VALUES ("I ate breakfast.");

The id and event_time fields would get populated automatically. If you tried to insert without an event value, though, your transaction would be rejected. "Look, buddy, this table exists to store these data, and if you don't provide them, there's no point in make a new tuple."

Well, MySQL's behavior is actually to say, "Hey, you forgot to specify a default when you made that NOT NULL field. I'll add one for you." Each datatype has its own default default, and those get applied when you create your table. You can't cleverly say "DEFAULT NULL," because MySQL will stop you.

So, what am I reduced to? Well, I guess I could do something like this:

CREATE TABLE events ( id integer PRIMARY KEY SERIAL, event_time datetime NOT NULL DEFAULT current_time, event varchar(128) CHECK (event IS NOT NULL), );

So, event can be null, except the check constraint clause asserts that it must not be. I guess that would... no, wait... what?! I quote the fine manual:

The CHECK clause is parsed but ignored by all storage engines.

Right. Time to implement more data integrity in the application layer for now.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • This isn't fixed in MySQL 5. I've just been fighting the same problems.

    The solution, it seems, is to put the database into strict mode []. The description includes this:

    Make MySQL behave like a "traditional" SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column.

    But I can't understand why anyone would possibly want a database to not behave in the traditional manner.