Is there a way to not allow empty strings in the database for a data type. I
have a column set to not null but sometimes users enter empty strings which
are also unacceptable. How can I force MySQL to disallow empty strings in
addition to not null.

Ferindo

On Sat, Mar 22, 2008 at 5:03 PM, Ferindo Middleton
<ferindo.middleton@gmail.com> wrote:
> Is there a way to not allow empty strings in the database for a data type. I
> have a column set to not null but sometimes users enter empty strings which
> are also unacceptable. How can I force MySQL to disallow empty strings in
> addition to not null.
>
> Ferindo
>

A trigger should work for you.
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

A far better option would be validating in your app.

--
Rob Wultsch

Well, maybe my problem isn't necessarily on user input. My app does check
for this and not allow empty strings. I guess the problem more so exists
because I get files from the client to LOAD into the database. I have
several SQL scripts in a library that I choose from to load the data,
different scripts will be run based on the various formats the client sends
to me to load the data.
Maybe I should do a better job examining and tweaking my various scripts I
have for checking and handling such empty string values when loading files.
But I just wanted to build some kind of constraint into the database itself
so just in case I forget certain details, maybe the constraint would be
there within the database itself. I also plan to hand over such operations
to other admins eventually and I wanted the constraint to be in the
structure of the tables where necessary. I used to use PostgreSQL and I
think this feature was easier to command using CREATE TABLE.
I imagine this kind of feature would be an enhancement that could otherwise
be avoided by a more aggressive quality control by a db admin but I think it
would be a good idea to have some kind of "NOT EMPTY" constraint on a
database considering NULL and an empty string ( '' ) are separate values. In
the real world they really mean the same thing and the solution seems
obvious to put such a constraint at the database creation level (in the
structure) when you think about it in practical terms.
That way you could have a safeguard so developers and db admins wouldn't
have to worry about, especially when some them have very large sets of data
to manage and pass among to different organizations when trying to integrate
data between different systems, all of which, again, share the practical
idea that an empty string is equal in value to a NULL value.

Ferindo

On Sat, Mar 22, 2008 at 8:20 PM, Rob Wultsch <wultsch@gmail.com> wrote:

> On Sat, Mar 22, 2008 at 5:03 PM, Ferindo Middleton
> <ferindo.middleton@gmail.com> wrote:
> > Is there a way to not allow empty strings in the database for a data
> type. I
> > have a column set to not null but sometimes users enter empty strings
> which
> > are also unacceptable. How can I force MySQL to disallow empty strings
> in
> > addition to not null.
> >
> > Ferindo
> >
>
> A trigger should work for you.
> http://dev.mysql.com/doc/refman/5.0/en/triggers.html
>
> A far better option would be validating in your app.
>
> --
> Rob Wultsch
>



--
Ferindo Middleton
Web Services 2.0 Java Servlet Container Administrator and Application
Developer/MySQL and PostgreSQL Database Administrator/Infrastructure and
Integration Management Specialist specializing in Linux/MySQL/Apache Tomcat
web application development and open source solutions/Perception
Augmentation and Artificial Synapsis Control Supplementation Research
Specialist for AI Wetware-to-Software Interface and Design
-Sleekcollar-

On Mon, Mar 24, 2008 at 5:07 PM, Ferindo Middleton <
ferindo.middleton@gmail.com> wrote:

> Well, maybe my problem isn't necessarily on user input. My app does check
> for this and not allow empty strings. I guess the problem more so exists
> because I get files from the client to LOAD into the database. I have
> several SQL scripts in a library that I choose from to load the data,
> different scripts will be run based on the various formats the client sends
> to me to load the data.
> Maybe I should do a better job examining and tweaking my various scripts I
> have for checking and handling such empty string values when loading files.
> But I just wanted to build some kind of constraint into the database itself
> so just in case I forget certain details, maybe the constraint would be
> there within the database itself. I also plan to hand over such operations
> to other admins eventually and I wanted the constraint to be in the
> structure of the tables where necessary. I used to use PostgreSQL and I
> think this feature was easier to command using CREATE TABLE.
> I imagine this kind of feature would be an enhancement that could
> otherwise be avoided by a more aggressive quality control by a db admin but
> I think it would be a good idea to have some kind of "NOT EMPTY" constraint
> on a database considering NULL and an empty string ( '' ) are separate
> values. In the real world they really mean the same thing and the solution
> seems obvious to put such a constraint at the database creation level (in
> the structure) when you think about it in practical terms.
> That way you could have a safeguard so developers and db admins wouldn't
> have to worry about, especially when some them have very large sets of data
> to manage and pass among to different organizations when trying to integrate
> data between different systems, all of which, again, share the practical
> idea that an empty string is equal in value to a NULL value.


An alternative that I do not like much, but should work for you:
CREATE TABLE `t` (
`c1` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`c2` VARCHAR( 255 ) NOT NULL
);

DROP PROCEDURE IF EXISTS t_insert;
DELIMITER |
CREATE PROCEDURE t_insert(IN val VARCHAR(255))
BEGIN
IF LENGTH(REPLACE(val,' ','')) =0 THEN
SET val = NULL;
END IF;
INSERT INTO t(`c2`)VALUES (val);
END

mysql> call t_insert('stuff');
Query OK, 1 row affected (0.00 sec)

mysql> call t_insert('');
ERROR 1048 (23000): Column 'c2' cannot be null
mysql> call t_insert(' ');
ERROR 1048 (23000): Column 'c2' cannot be null
mysql> call t_insert(' blah ');
Query OK, 1 row affected (0.00 sec)






--
Rob Wultsch
(480)223-2566
wultsch@gmail.com (email/google im)
wultsch (aim)
wultsch@hotmail.com (msn)