From: Dave Burt <dave@...>
Date: 2005-10-29T09:32:05+09:00
Subject: Re: [QUIZ] TumbleDRYer (#53)

Bill Guindon wrote:
> Since I had to rtfm [1] for a couple simple SQL questions (yeah, my
> SQL is probably rustier than most), thought I'd save others the time.
>
> q) Why doesn't the 'description' field have a 'default'?
> a) "BLOB and TEXT columns cannot be assigned a default value."

I'm pretty sure that's DBMS-specific (MySQL), and I'm pretty sure SQLite 
(for example) allows this. I'm actually not sure about other major DBMSes.

> q) How should you treat columns that allow NULLs (none of the examples 
> do)?
> a) "If neither NULL nor NOT NULL is specified, the column is treated
> as though NULL had been specified."

That's standard SQL. Most schema-DDLs include optional columns; I like to 
omit both the NULL and the NOT.

While we're looking at the MySQL DDL:

q) What's with the backticks (`)?
a) That's MySQL's way of quoting column names. They're unnecessary as long 
as the name is alphabetic and not a keyword. I don't know of any other DBMS 
that uses backticks for this.

q) What's with the TYPE=?
a) Again, a MySQL feature - MySQL supports different storage engines (and 
here was I thinking a DBMS was supposed to manage storage...) and this 
specifies which to use. MyISAM is the crippled default one which doesn't 
support referential integrity.

q) Why doesn't auto_increment work with other databases?
a) Because defining an auto-increment field is not specified in SQL-92. They 
can all do it, though.

q) Why are data types, "default" and "auto_increment" in lowercase when 
other keywords are uppercase?
a) I don't know.

For what it's worth, MySQL 5 is a lot better than MySQL 4 (now with in-line 
views, I believe), but I'd still recommend Postgresql or really anything 
else over it.

Cheers,
Dave