Re^2: Typeless bind fails for certain large numbers with MySQL decimal

I disagree. Most databases (and finally in MySQL 5.0.2 and up) have built in constraint checks that are very valuable. I'm not a US citizen but I strongly suspect a ZIP code containing letters makes little sense. I feel the database should not allow values not adhering to the column specification.

mysql> drop table if exists foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (i numeric(23,0) primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   | 
+-------------------+
1 row in set (0.00 sec)

mysql> insert into foo values ('a');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values ('d');
ERROR 1366 (HY000): Incorrect decimal value: 'd' for column 'i' at row
+ 1
mysql>
[download]

--
No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]

Comment on Re^2: Typeless bind fails for certain large numbers with MySQL decimal Download Code

Replies are listed 'Best First'.
Re^3: Typeless bind fails for certain large numbers with MySQL decimal by dragonchild (Archbishop) on May 28, 2008 at 19:17 UTC
Type constraints are different from check constraints. All databases provide type constraints, MySQL allowing you to turn them on or off as desired. Check constraints, however, are more comprehensive. In this case, let's say you have a DECIMAL(23) for this 19 digit account number. What's preventing a 12 digit entry? In other words, the application will need to do a good amount of validation anyways. Anything the database provides in terms of type constraints is bonus. Check constraints, imho, are better implemented as a service wrapping the database connection or as a set of libraries. I personally don't like embedding constraints in the database because I feel that puts too much work into the datastore. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]

Replies are listed 'Best First'.

Re^3: Typeless bind fails for certain large numbers with MySQL decimal
by dragonchild (Archbishop) on May 28, 2008 at 19:17 UTC

In this case, let's say you have a DECIMAL(23) for this 19 digit account number. What's preventing a 12 digit entry? In other words, the application will need to do a good amount of validation anyways. Anything the database provides in terms of type constraints is bonus. Check constraints, imho, are better implemented as a service wrapping the database connection or as a set of libraries. I personally don't like embedding constraints in the database because I feel that puts too much work into the datastore.

My criteria for good software:

Does it work?
Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

[reply]