23 replies to this topic

[BASHERS33]

I usually set fields to not null, but I never understand exactly when I need to and what the implications are, even if I read about it.

It's my understanding that you use not null for any field which has a default value or which would be in any insert? And update queries are fine either way?

And then if I use php and use if( $field != "" ) will it give the same result regardless of whether the field has NULL or an empty string/nothing for its value?

I justw ant to be sure that if someone is using strict then they don't run into problems, but at the same time if I change some fields to NULL then I am worried my php will not work properly.

[BlaineSch]

I am not totally sure but im pretty sure if its "not null" it has to be set when inserting and doing '(field="")' actually does set it to a value a null value is literally nothing - no text is still something.

[BASHERS33]

What confuses me though is I am almost positve that I have had it be considered == "".

How would I even do a check like that if it could be null and I want null to fail when checking to be sure something is there?

Or what if I use not null and I include the field on insert, but I set it to nothing? If I set it to nothing (empty string) would it be fine in strict or does it flat out have to be "something" and not simply be included in the insert?

I don't see what the point is of having strict mysql and php modes anyway. What in the world good is it to simply not allow inserts with some fields not set anyway?

I do know any field with a default value is fine as not null. I'm still confused why NULL passed a == "" test though as if it had no value. Maybe I am not remembering correctly.

[WingedPanther]

Many languages offer an IsNull function for this. I don't know the PHP version off hand.

You should only set a field to be NOT NULL if having a field be null represents a logic error. Also, it's important to be aware that some databases (Oracle comes to mind) consider "" to be the same as NULL.

Here's my rule of thumb: Primary keys (should be) NOT NULL automatically. Any other field where NULL represents incomplete or illogical data should also be NOT NULL.

Default values are a separate issue: I only set a default value if there is a rational value to set. Integers can often default to 0, strings can often default to a common list value. Being NOT NULL doesn't imply you should have a default value, however. Consider a Forum table:

CREATE TABLE USERS {
  UID          INTEGER NOT NULL;
  USER_NAME    VARCHAR(30) NOT NULL;
  REPUTATION   INTEGER DEFAULT=0;
  EMAIL        VARCHAR(100) NOT NULL;
  POSTS        INTEGER DEFAULT=0;
  PRIMARY KEY (UID)
}

Having defaults for POSTS and REPUTATION makes sense. When created, those values will always be 0, so there's no reason to have to specify every time. Many people like to have a GUID for the primary key, but you still don't want to allow NULL for USER_NAME or EMAIL. If those got in as NULL, something horribly wrong has happened. They should never have a default, because that will disable their ability to act as warnings about errors. It would be reasonable to add an additional constraint that USER_NAME must be distinct.

[BASHERS33]

Well I am talking about with mysql and the way it was explained to me in the past was that default values are what makes the difference. That if someone is using mysql strict mode and an insert does not include anything for fieldx then it will error out UNLESS fieldx has a default value, then it will put that value in and not cause any errors.

It may indeed not be related to whether it's null or not though. I can't remember for sure about that part.

[WingedPanther]

NOT NULL + no default + no value specified = SQL error, not PHP error.

[BASHERS33]

I wasn't saying it was a php error. I was asking how nukll passes and fails in php. Now someone told me NULL is == "" but NULL is not === "". I almost never use === anyway so my checks should still work for null fields.

As far as sql I am asking what I have to do to avoid the errors. Someone told me what he does is if it's not an integer field he makes it a default of NULL. So that may be why I was confusing NULL and sql errors.

If I set a field to "" in an insert will it fail or not in strict? Does it pass the "field being set" condition even if it's being set to nothing?

[BASHERS33]

I'm looking in the DB at how the forum software does it and it "seems" that what they do is if the field is not an integer type of field then:
1. if the field is always going to have a value on insert, it's still set to not null.
2. if the field MAY or MAY NOT have a value then it is set to NULL and default of NULL.

Am I still misunderstanding? Although in one case I saw a comment had the comment field set to null! A comment would obviously ALWAYS have a value for the comment. Then another table had something similar and it had a title field NOT set to null by default.

But am I on the right track? Do I need to look at each of my isnerts and be sure everything will be checked and have a value and if it sometimes won't then change that field to default of null?

edit: by the way when changing a field type in a table that already has rows in it which ones will keep the data and which will get rid of it? I would think int -> varchar would still keep the numbers, but I think I tried that one time and it didn't keep it.

Also if I change a not null field to null field and it already has data will it cause an issue? This is all for mysql 5.

Edited by BASHERS33, 10 May 2009 - 03:09 AM.

[BASHERS33]

Sorry, too late to edit.

I am still very confused. I set a field to NULL which won't always have a "value". But that field is still always in the INSERT. So if it has no value in the file then it's still inserting its value as nothing even if I have it set to be a field with default NULL.

If you have a field name included in an idnert then default values don't even matter! So apparently if I have EVERY field name listed in an insert then even if they are set up to set the value to nothing then it won't fail in mysql strict?

So bottom line, to avoid mysql strict errors can i simply have all fields listed in inserts even if they have no value to set? Can I set defaults to NULL and then not ened to include them at all? Can I set them to NULL and still include them and if they have no value still it won't error out?

My understanding currently is this: the only way it errors out in mysql strict is if the field has no default value and ALSO is not even MENTIONED in the insert, but if you list it in the insert to set it, but you set it to nothing ( "" ) it's still ok. So if I understand properly any field I will not even MENTION in an insert needs a default value. And then also even if it is mentioned in it then NULL is still recommended when the info may or may not be known? I hope I am on the right track because this has been 3 days of confusion.

edit: I searched to try to find the answer, but I still can't find every answer. I did find documentation stating that if you set a default value to NULL then it will use that and prevent errors. However what I still ened to know is if I set a value to '' in my insert will it still error out or will it consider it "set"? What if a form field is OPTIONAL? Well if I have the insert set the value to the what was typed on the form, if nothing is allowed, will it allow nothing to be what's set in the DB or do I have to somehow set it to NULL in the actual php if it's empty?

Edited by BASHERS33, 11 May 2009 - 03:38 AM.

[WingedPanther]

I think you are looking for a "rule" that doesn't really exist. Whether something is NOT NULL depends on the nature of the data. Whether a NOT NULL field requires a default depends on the types of SQL statements you will use, and the nature of the data.

You may want to give a sample table, and explain your thinking on whether each field should be NOT NULL and whether it should have a default value.

[BASHERS33]

I'm not really talking about null vs. not null anymore. I'm asking what's going to gice errors in mysql strict.

If I use:

$something = "":

'field' => $something

where that second line is part of the insert will it give a strict error or not? Does something have to be something or can it be nothing and still be considered "set" and thus cause no errors? That is my question.

The defaults obviously only matter when a field is not set in the insert. In the above case will it error out or not? That's my question. Simple as that. If it errors out then apparently I have to set $something to NULL if it's an empty string?

[jhonichen]

I've heard from some forums saying that it's better to set the value to NOT NULL because it'll save more space, for varchar/char etc, we better set it to NOT NULL with DEFAULT '' (empty string).

on insert, if we do not have value for that field, we don't need to set the value for it.

for example

create table test (
test_id int(11) NOT NULL AUTO_INCREMENT,
test_name varchar(100) NOT NULL DEFAULT '',
description varchar(500) NOT NULL DEFAULT '',
PRIMARY KEY(test_id)
)

if we do not have value for description,
we can use query

INSERT INTO test (test_name) VALUE ('bla bla')

above query will not bounce error out.

or in php, using Zend Framework for instance:

$dataToInsert = array(
"test_name"=>"bla bla",
)
$db->insert('test', $dataToInsert);

//or below will work too
$dataToInsert = array(
"test_name"=>"bla bla",
"description"=>"",
)
$db->insert('test', $dataToInsert);