Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL: how to enforce the NOT NULL constraint

 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 50277
80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 50277
80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. I hadn't realised that MySQL doesn't implement CHECK constraints.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic