[thelist] mysql: unique constraing and null default

Aleem Bawany aleem.bawany at utoronto.ca
Sat Mar 1 11:57:04 CST 2003

>> What should I do? (this sql biz is no fun)
> take up a career with no exposure to sql?
> ;o)

It always seems to pop up, one way or the other. Hopefully
it'll start growing on me soon (I found a really interesting
read: http://www.extropia.com/tutorials/sql/toc.html )

> as always, when in doubt, test
> i'm happy to report that it does work in mysql --

I'm happy too. I was previously using '' instead of
NULL, thinking '' would default to NULL since NULL
was set as the default.

I now read up and have realized that to have a default
value inserted I must use something like this:

CREATE TABLE defaultvaluetest (
  id smallint(3) NOT NULL auto_increment,
  name char(3) NOT NULL default 'ano',
  handle char(3) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY handle (handle)

INSERT INTO defaultvaluetest(id,name,handle)
       VALUES (NULL,'foo',foo');
# next two resort to default name
INSERT INTO defaultvaluetest
       SET handle='bar';
INSERT INTO defaultvaluetest (handle)

SELECT * from defaultvaluetest;

1 foo foo
2 ano bar
3 ano joe

Worthy of mention, for other sql amateurs like myself.


[ http://aleembawany.com/ ]

More information about the thelist mailing list