[thelist] mysql: unique constraing and null default

[Aleem Bawany]

>> 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)
       VALUES(joe');

SELECT * from defaultvaluetest;

1 foo foo
2 ano bar
3 ano joe

Worthy of mention, for other sql amateurs like myself.

aleem

[ http://aleembawany.com/ ]