Question about database value checking

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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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?

thanks,
andy


--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Question about database value checking

--_080b0c2b-e08b-42a3-8fcb-a12e70628f92_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Are you using the strict SQL mode? Check your my.cnf file.
=20
Peter
=20
> Date: Fri=2C 4 Feb 2011 14:08:01 -0800
> From: awallace@ihouseweb.com
> To: mysql@lists.mysql.com
> Subject: Question about database value checking
>=20
> So=2C a problem popped up today that has caused us no end of hair-pulling=
=2C and
> it brought to mind a similar issue that I found very=2C well=2C wrong.
>=20
> If you have a table defined:
>=20
> CREATE TABLE `tester_table` (
> `acnt` varchar(20) NOT NULL DEFAULT ''=2C
> `method` varchar(10) NOT NULL DEFAULT ''=2C
> `card_num` varchar(100) NOT NULL DEFAULT ''=2C
> PRIMARY KEY (`acnt`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
>=20
> And try this:
>=20
> INSERT INTO tester_table (acnt=2C method=2C card_num)
> VALUES ('test1'=2C 'push'=2C NULL)=3B
>=20
> That fails. and gives a nice error. But:
>=20
> INSERT INTO tester_table (acnt=2C method=2C card_num)
> VALUES ('test1'=2C 'push'=2C 'A12345')=3B
>=20
> UPDATE tester_table set card_num =3D NULL WHERE acnt =3D 'test1'=3B
>=20
> That succeeds=2C 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=2C the following table:
>=20
> CREATE TABLE `tester_table2` (
> `acnt` varchar(20) NOT NULL=2C
> `required` enum('NO'=2C'DETAILS'=2C'RESULTS') NOT NULL=2C
> PRIMARY KEY (`acnt`)
> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8
>=20
> Lets you insert:
>=20
> INSERT INTO tester_table2 (acnt=2C required) VALUES ('A123456'=2C 'THIS I=
S NOT REAL')=3B
>=20
> Though it just puts an empty string into the "required" column.
>=20
> Is there a setting for mysql to return errors in these cases? It seems si=
lly to set
> up an enum column=2C or a not null column=2C and not have the possible va=
lues enforced?
>=20
> thanks=2C
> andy
>=20
>=20
> --=20
> Andy Wallace
> iHOUSEweb=2C Inc.
> awallace@ihouseweb.com
> (866) 645-7700 ext 219
> --
> There are two ways to build software:
> Make it so simple that there are obviously no bugs=2C
> or make it so complex that there are no obvious bugs.
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dphe1129@hotmail.com
>=20
=

--_080b0c2b-e08b-42a3-8fcb-a12e70628f92_--

Re: Question about database value checking

Thanks Peter, exactly what I was hoping for!
andy

On 2/4/11 3:11 PM, Peter He wrote:
>
> Are you using the strict SQL mode? Check your my.cnf file.
>
> Peter
>
>> Date: Fri, 4 Feb 2011 14:08:01 -0800
>> From: awallace@ihouseweb.com
>> To: mysql@lists.mysql.com
>> Subject: Question about database value checking
>>
>> 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`)
>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>>
>> 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`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>>
>> 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?
>>
>> thanks,
>> andy
>>
>>
>> --
>> Andy Wallace
>> iHOUSEweb, Inc.
>> awallace@ihouseweb.com
>> (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.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=phe1129@hotmail.com
>>
>

--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org