MySQL: how to enforce the NOT NULL constraint

[Tejas Jain]

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]

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]

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]

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]

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]

Thank you. I hadn't realised that MySQL doesn't implement CHECK constraints.

[Paul Sturrock]

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 ]