Can anybody give me an example when to use
allow nulldefault 0default ''andempty string.
In which situations should use these different configurations?
-
I'm confused, the way you wrote that it seems like you're trying to use them all together. You want to know when you'd use each one separately?animuson– animuson ♦2011-08-30 15:51:53 +00:00Commented Aug 30, 2011 at 15:51
-
yes. it is exactly what i wanna knowTural Ali– Tural Ali2011-08-30 15:54:31 +00:00Commented Aug 30, 2011 at 15:54
Add a comment
|
2 Answers
In general, avoid NULLs. NULL tends to require extra coding effort. Treatment for NULL versus empty string varies by RDBMS. Sorting of NULL within a set varies by RDBMS.
That said, you may wish to: Use NULLs on foreign key columns when the related row is optional.
Use NULLs when you want values to be eliminated from aggregate operations. For example, if you have an "age" column, but don't require this information for all records, you would still be able to get meaningful information from: SELECT AVG(age) FROM mytable
Use NULLs when you need ternary logic.
3 Comments
Icarus
"In general, avoid NULLs"? No way! I would say the opposite: If you don't have a value for something, let the value be NULL. This not only is semantically more accurate but also allows you to improve performance in your queries. Is not the same thing testing for NULL than testing for ''.
animuson
Here is another example from the related list at the right. I've always used an empty string because 1) I've never had a need to use NULL and 2) I don't have that many fields in my databases that can even be left empty.
1.A NULL value represents the absence of a value for a record in a field (others softwares call it also a missing value).
2.An empty value is a "field-formatted" value with no significant data in it.
3.NULL isn't allocated any memory, the string with NUll value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".
4.Null has no bounds, it can be used for string, integer, date, etc. fields in a database. Empty string is just regarding a string; it's a string like 'asdfasdf' is, but is just has no length. If you have no value for a field, use null, not an empty string.
5.Null is the database's determination of an absense of a value logically, so to speak. You can query like: where FIELD_NAME is NULL