Win a copy of Succeeding with AI this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
  • Junilu Lacar
Sheriffs:
  • Tim Cooke
  • Jeanne Boyarsky
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • salvin francis
  • fred rosenberger
  • Frits Walraven

MySQL: how to enforce the NOT NULL constraint

 
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 ]
 
Marshal
Posts: 25471
67
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.
 
Marshal
Posts: 69030
275
  • 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.
 
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
Marshal
Posts: 69030
275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. I hadn't realised that MySQL doesn't implement CHECK constraints.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE 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 ]
 
Here. Have a potato. I grew it in my armpit. And from my other armpit, this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic