different column attributes for default values

Question

Can anybody give me an example when to use

allow null
default 0
default '' and empty 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, Aug 30 '11 at 15:51

score 2 · Accepted Answer · answered Aug 30 '11 at 16:06

2

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.

answered Aug 30 '11 at 16:06

Jamey

1,595
9
23

1

"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 ''. – Icarus Aug 30 '11 at 16:09
1

I can see the points of both Jamey and @Icarus. I would add that in a 6th normal form database, you should be able to design the structure such that there are no nulls or blank fields. It's rather awkward to do though, and in my experience, often isn't worth the effort. [more info](http://stackoverflow.com/questions/4336687/how-can-i-avoid-nulls-in-my-database-while-also-representing-missing-data) – Spycho Aug 30 '11 at 16:13
Here is [another example](http://stackoverflow.com/questions/167952/null-or-empty-string-to-represent-no-data-in-table-column) 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. – animuson Aug 30 '11 at 19:09

score 1 · Answer 2 · answered Aug 30 '11 at 15:52

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

different column attributes for default values

2 Answers2