Anyone is anyone who uses this forum. I dont ask any particular person as usual! Otherwise I would have add his nick on topic!
DBAtrix I am not just taking a poll, I am asking because one of application we are using hasnt got any name convention for Not Null constraints and I was wondering if people name these constraints at all, I dont, I only name PK, FK and UK like you :D
Actually, this should have been a new thread. Regardless, the premise of the question seems to be designing a database to handle NULLS or not, pros and cons.
I have actually worked at a place where the database designers had deigned it imperative that NULLs not be allowed. This was many years ago, before becoming a database designer myself. I tried to use logic to show them the error of their ways, but to no avail. Obviously, I am a proponent of NULLs. But let's check out the differences:
NULLs do *not* appear in indexes. This means that doing a WHERE Col1 IS NULL is guaranteed to give you a tablescan, regardless of the number of rows that actually contain NULL. If you instead have a default value for every column, then this non-null value obviously *will* appear in indexes and can be searched on. Very handy. 1 Point for NOT NULLs.
If every column is NOT NULL, then every column needs some kind of default. This default must obviously vary by datatype, but also by usage. while -1 is a fine default for an Age field, it will not work for a StockPriceDelta field. So if you do have a numeric field that can span the entire range of positive and negative values, what default do you give it? Often 9999. This extra consideration and maintenance of defaults is a point against NOT NULLs.
Back to the indexes. If we give a default value of 9999, we are seriously messing with the statistics for said index. If most of the values are between -10 and 10, the index will know this and act accordingly. Now we put a bunch of 9999 values in there. It will now assume that all the values are evenly distributed between -10 and 9999 - Quite the wrong idea. Therefore, if you SELECT where Col1 Between -9 and 10, while you will actually be getting 90% of the data, the optimizer will assume you are getting only 1% of the data and build a bad plan for you. Another point against NOT NULLs.
Back to the Age column I mentioned before. How do I find all the minors in my system? WHERE Age<18. Doh! Can't do that, because the default is -1. Therefore, all the people whose ages we do not know will be -1 and will be counted as minors - wrong answer. Therefore, the developer's now have to know about the default imposed by the designers. This is not a good example of isolation. Every query written against such a system must know about the default values for every column in the system - and remember, each column may have a different default value. Another point against NOT NULLs.
Back to the Age column. The answer to 'Is someone a minor' has 3 answers: Yes, No, and I don't know. I don't know is represented by NULL. This is the basis for the 3-valued logic system of databases. The fact is that we do not know the value for every field in our database. NULL represents this nicely, and support for it is built into every aspect of the database (with the exception of indexes - oversight on the part of Oracle that with any luck, they will eventually rectify). If you decide not to use the NULL, you have not eliminated the issue. You have simply decided to solve it yourself - manually. Anything is possible, but is the payoff worth the effort?
Finally, (venting time) the ultimate argument that my former company made for NOT NULLs was the EXISTS function. Whereas the rest of the database lives in a 3-valued world, the EXISTS function can only return TRUE or FALSE. Some idiot with a typewriter wrote an article that showed that if you did
WHERE NOT EXISTS...
you didn't get every row back.... Shocker!! :)
Sorry, but no s*$& Sherlock!
Just like if you do
WHERE Age >= 18
WHERE Age < 18...
you don't get every row. This is not rocket science, this is the premise of asking for the rows that are YES and the rows that are NO, but neglecting to ask for the rows that are I DON'T KNOW. This would be classified as a user error.
And just because the EXISTS does not allow you to ask the I DON'T KNOW question is no reason to throw out the entire system. You simply work around it!
Phew! Sorry for the rant, people, but that was one of those repressed childhood memories that you just brought to the surface :).
Hope this helps,
(Actually, I hope this is even what you asked about :) )