Hi
I was wondering if anyone name NOT NULL constraints in all tables? Or just name PK, FK and UNIQUE constraints?
Printable View
Hi
I was wondering if anyone name NOT NULL constraints in all tables? Or just name PK, FK and UNIQUE constraints?
YEs u can name not null constraint to likeQuote:
Originally posted by pando
Hi
I was wondering if anyone name NOT NULL constraints in all tables? Or just name PK, FK and UNIQUE constraints?
create table emp
(empno numberconstraint c1_nn not null,
ename varchar2(10));
u can name any constraint type.PK,Fk,not null,check or uniue.
kuckoo
err I know that I mean if anyone name Not Null constraints other than PK, FK and UK or just them as SYS_29392392 for example
Who is "anyone"?
pando, are you just taking a poll to find out how people do things?
i must admin to not naming NOT NULL constraints, but i always name unique, fk, and pk constraints. i suppose because i regularly reference UQ, FK, & PK's by name, but rarely NN constraints.
d.
[Edited by DBAtrix on 02-16-2001 at 02:09 PM]
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
OK. If I use case tool to generate DDL, then I would add Not NULL Constraint Name in the script itself. This will be easy to maintain NN constraint.
The system generated constraint name (SYS_COXXXXX) is hard to remember.
I have seen a system use NOT NULL for almost all columns of all tables? Is it a bad design? Why?
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:
Indexes
---------
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.
Defaults
----------
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.
Indexes
---------
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.
WHERE clause
----------------
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.
3-Valued Logic
-----------------
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?
EXISTS
---------
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
SELECT
WHERE EXISTS
UNION
SELECT
WHERE NOT EXISTS...
you didn't get every row back.... Shocker!! :)
Sorry, but no s*$& Sherlock!
Just like if you do
SELECT
WHERE Age >= 18
UNION
SELECT
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,
- Chris
(Actually, I hope this is even what you asked about :) )
[Edited by chrisrlong on 03-01-2001 at 05:11 PM]
If you use BIT-MAPPED INDEX and the column has NULL Value, then Index will be used. What you said is applicable for only B-Tree Index.
Yes, you are absolutely correct - sorry for the ommission. I generally only deal with OLTP databases, however, so I usually avoid BITMAP indexes like the plague :).
- Chris
sorry for stupid cinsideration regarding EXISTS operator:
from my knowledge EXISTS brings into action something more than boolean logic (sorry, I do not know the exact term in english of it, may be "first predicate logic").
I just wandering what interpretation could receive
'I DO NOT NO IF ROW EXISTS'?
Are these rows for which WHERE clause yields NULL?
I'm not entirely sure I understand the full question, but I will elaborate on the issue as I understand it. The problem is that, from what I remember, it was a completely illogical argument, so trying to justify it is going to be difficult.
If you want to find out everything you know about minors in your database, you might do
SELECT COUNT(*), "Adults" FROM PEOPLE WHERE AGE >= 18
UNION
SELECT COUNT(*), "Minors" FROM PEOPLE WHERE AGE < 18
UNION
SELECT COUNT(*), "Unknown" FROM PEOPLE WHERE AGE IS NULL
Of course, you would never do this this way, but it clearly shows the difference between the 3 answers: Yes/No/Unknown
Now, what if you had a different type of query that wanted to test whether or not you had any minors? Say you had to verify that your mailing list had no minors before you sent an advertisement for cigarettes. The following might be a good query:
SELECT 'HaveMinors' FROM DUAL WHERE EXISTS
(SELECT 1 FROM PEOPLE WHERE AGE < 18).
This, of course, would not take into account the people whose age you do not know. Somehow, this confused the author of some pointless article many years ago. You can obviously find out if there are people whose age you don't know by
SELECT 'HaveUnknowns' FROM DUAL WHERE EXISTS
(SELECT 1 FROM PEOPLE WHERE AGE IS NULL).
Since EXISTS can only return TRUE or FALSE, it cannot answer that it 'doesn't know'. The correct answer is to re-word the question: Are there any rows where we don't know the value of the fields involved in the predicate? So, just like everywhere else, if you want to find the NULLS, you have to go looking explicitely for them - No big deal.
There might have been something more to the argument, but it was a long time ago and I don't remember the details. I just remember that it was pretty pointless and easy to work-around - *much* easier than all the damn hoops we had to jump through to handle the various default values for all the NOT NULL columns.
- Chris