wood burning stoves 2.0*
The moose likes JDBC and the fly likes MySQL: how to enforce the NOT NULL constraint Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL: how to enforce the NOT NULL constraint" Watch "MySQL: how to enforce the NOT NULL constraint" New topic
Author

MySQL: how to enforce the NOT NULL constraint

Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
I created a simple table in MySql:

CREATE TABLE USER (
USER_ID INT NOT NULL AUTO_INCREMENT,
USER_LOGIN_NAME VARCHAR(100) NOT NULL,
USER_NAME VARCHAR(100) NOT NULL,
USER_EMAIL VARCHAR(100) NOT NULL,
USER_PHONEVARCHAR(100)
PRIMARY KEY(USER_ID)
);

How can I enforce the NOT NULL constraint on USER_NAME column so that no empty string can be inserted? Use 'no default'? MySql inserted an empty string if there is no value provided for USER_NAME?
[ October 31, 2008: Message edited by: Todd Jain ]

"Knowing is not enough, you must apply... Willing is not enough, you must do."
--Bruce Lee
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

An empty string (one with zero characters) is not null, so that doesn't violate the constraint in the database. You could certainly write some validation logic in wherever the data is coming from to not allow empty strings there.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39053
    
  23
Or a check constraint; there are probably String length routines already built into MySQL but I can't remember what they are called.
Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
Originally posted by Paul Clapham:
An empty string (one with zero characters) is not null, so that doesn't violate the constraint in the database. You could certainly write some validation logic in wherever the data is coming from to not allow empty strings there.


In Oracle, you cannot insert an empty string into a NOT NULL column. An empty string is treated NULL.
S. Palanigounder
Ranch Hand

Joined: Apr 03, 2003
Posts: 145
NULL is not an empty string.

This is one of the short come of MySql: It has not yet implemented CHECK CONSTRAINTs so you have to enforce requirements like those for non-empty strings in the application layer until it fills that hole. A workaround is to have the application layer change empty strings to NULLs so that MySQL returns on error on such INSERTs.

I think MySql is still free.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39053
    
  23
Thank you. I hadn't realised that MySQL doesn't implement CHECK constraints.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


This is one of the short come of MySql: It has not yet implemented CHECK CONSTRAINTs so you have to enforce requirements like those for non-empty strings in the application layer until it fills that hole. A workaround is to have the application layer change empty strings to NULLs so that MySQL returns on error on such INSERTs.

I think the new SQL Modes may work round this bug.
[ November 03, 2008: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
GeeCON Prague 2014
 
subject: MySQL: how to enforce the NOT NULL constraint