List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:February 4 2011 10:08pm
Subject:Question about database value checking
View as plain text  
So, a problem popped up today that has caused us no end of hair-pulling, and
it brought to mind a similar issue that I found very, well, wrong.

If you have a table defined:

CREATE TABLE `tester_table` (
   `acnt`            varchar(20) NOT NULL DEFAULT '',
   `method`          varchar(10) NOT NULL DEFAULT '',
   `card_num`        varchar(100) NOT NULL DEFAULT '',
   PRIMARY KEY (`acnt`)

And try this:

INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', NULL);

That fails. and gives a nice error. But:

INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', 'A12345');

UPDATE tester_table set card_num = NULL WHERE acnt = 'test1';

That succeeds, but it puts an empty string into the card_num column. I
would have thought (hoped) that an error would be thrown in that case as
well.  On a similar note, the following table:

CREATE TABLE `tester_table2` (
   `acnt` varchar(20) NOT NULL,
   `required` enum('NO','DETAILS','RESULTS') NOT NULL,
   PRIMARY KEY (`acnt`)

Lets you insert:

INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT REAL');

Though it just puts an empty string into the "required" column.

Is there a setting for mysql to return errors in these cases? It seems silly to set
up an enum column, or a not null column, and not have the possible values enforced?


Andy Wallace
iHOUSEweb, Inc.
(866) 645-7700 ext 219
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
Question about database value checkingAndy Wallace4 Feb
  • RE: Question about database value checkingPeter He5 Feb
    • Re: Question about database value checkingAndy Wallace5 Feb