UPDATE & NULL (Beginner)

[Anon]

I'm a beginner, and really need some assistance.

We have a membership form on our site that people can update -- and blank values should be okay for some of the fields.

However, my database won't accept empty values when I do an UPDATE.

The error I receive is:

Field 'RESULTS.HighSchool' cannot be a zero-length string.

My QUERY is currently:

UPDATE RESULTS
SET
FirstName='::FirstName::',
LastName='::LastName::',
EmailAddress='::EmailAddress::',
Password=':😛assword::',
HighSchool='::HighSchool::'
WHERE ID = ::ID::

Can someone help me? I'm stuck.

Thank you.

[brandonschnell]

when you created the table did you specify some columns as being "NOT NULL" (ie create table test (null int, not_null int not null)? run "describe table_name" and see if the columns are allowed to be null.

if that doesn't help give us a snippet of your code and how your table was created.

[killswytch]

If the fields in the database are 'not null' or what Sara described, do this:

alter table tablename modify Highschool varchar(20) default null;

or something along those lines.

[Anon]

Thank you so much for your help. I will try your suggestions.

Sara

[[deleted]]

Which database type are you using?
(Oracle, informix etc..)

It looks like there is a constraint on that column.
That means the table will not accept anything that does not meet a few rules. In this case you may not fill the column with a zero length string.

The only way around this is to either change the table setup and remove the constraint (bad idea unless you have no options left)
or to change the way you store your data.
Instead of an empty string you might store "none" or "empty"

Either way, I'd recommend you have a talk with whoever set up the database (unless you did that yourself, ofcourse 🙂 ) to see why this table is like it is.

[[deleted]]

Whoa there, I wouldn't just go around changing your table like that! Think before you act!
Before you can change anything, you need to know what the current setup is, and what your new setup needs to be.
For example, if you had CHAR(40) and you change it to CHAR(10), you loose 20 chars off all values in that column.
One shudders to think what happens if you change CHAR to INT...

But anyway,

Does a size constraint get released when the columns is modified?

Default NULL is a bad idea in any case.
For example, in MySQL, storing a NULL takes more space than storing an emtpy string.

Remember, a zero-length string is not a NULL.