Does anyone name Not Null constraints? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Does anyone name Not Null constraints?

  1. #11
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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


  2. #12
    Join Date
    Nov 2000
    Posts
    212
    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?


  3. #13
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width