1 2 3 Previous Next 34 Replies Latest reply: Dec 23, 2012 7:54 AM by sql_coder Go to original post RSS
      • 15. Re: insertion of null values in unique constraint column
        TPD-Opitz
        ranit B wrote:
        In that case, if comparing to 'NULL' will never be possible right?
        Thats why oracle has <tt>IS [NOT] NULL</tt> and <tt>nvl()</tt>

        bye
        TT
        • 16. Re: insertion of null values in unique constraint column
          Solomon Yakobson
          3360 wrote:
          So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
          Not true. SQL Server treats NULL same and even stricter than Oracle.
          with xx as(
              select 1 x, 1 y union all
              select 1 x, NULL y union all
              select NULL x, 1 y union all
              select NULL x, NULL y
          )
          SELECT x, y,
                      CASE WHEN x = y
                      THEN
                          'Equal'
                      WHEN x != y
                      THEN
                          'Not - Equal'
                      ELSE
                          'Unknown'
                      END
           FROM xx;
          x     y     (No column name)
          1     1     Equal
          1     NULL     Unknown
          NULL     1     Unknown
          NULL     NULL     Unknown
          SY.
          • 17. Re: insertion of null values in unique constraint column
            6363
            Solomon Yakobson wrote:
            3360 wrote:
            So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
            Not true. SQL Server treats NULL same and even stricter than Oracle.
            The full quote was
            3360 wrote:
            Ikrischer wrote:
            and yes NULL is strange in ORACLE, by default other dbms wont allow more then one NULL value on a column with a unique constraint.
            So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
            If this statement, which I did not make, is incorrect

            >
            by default other dbms wont allow more then one NULL value on a column with a unique constraint
            >

            Then the implication of it that I highlighted is also incorrect, but I don't have SQL server to check and I never said it was an accurate statement, just that if true the behavior described is completely wrong.

            Doesn't taking quotes out of context to change their meaning and then pointing out that something someone didn't actually say is wrong lose it's appeal after a while?
            • 18. Re: insertion of null values in unique constraint column
              sql_coder
              3360 wrote:
              well partially incorrect can be more confusing then completely incorrect, because you dont have a straight line ^^
              Agreed, though I still prefer being partially correct to completely wrong.
              the point about the NULL discussion is, there is not natural correct or natural wrong. It is the same discussion about food, if pizza or pasta is more tasty. There is only one good way to handle NULL in different situations, that is to have a standard set by ANSI and all DBMS are handling it that way. Think about the old Oracle join syntax, it was not wrong (unfortunality some people are still using it), but standards are importend, specially for SQL, so they changed it to ANSI join syntax in 9i.

              Ikrischer
              • 19. Re: insertion of null values in unique constraint column
                6363
                Ikrischer wrote:
                3360 wrote:
                well partially incorrect can be more confusing then completely incorrect, because you dont have a straight line ^^
                Agreed, though I still prefer being partially correct to completely wrong.
                the point about the NULL discussion is, there is not natural correct or natural wrong.
                I disagree, anyone who comes away from the concept of NULL thinking that NULL is equal to NULL and that all NULLS are equal is fundamentally and completely wrong about their understanding of NULL.
                It is the same discussion about food, if pizza or pasta is more tasty.
                That analogy is complete garbage in my opinion. My opinion is is a personal preference like how I rate the taste of food, it is nothing like a scientific or mathematical concept no matter how imperfect.
                There is only one good way to handle NULL in different situations, that is to have a standard set by ANSI and all DBMS are handling it that way.
                It must be nice to live in an idealistic world with committees that work for the benefit of all and no doubt world peace is achievable.

                Where I live the ANSI standard was arrived at by a committee of competing database vendors whose only common goal was to punish any competitor who innovated by standardizing on alternative methods of achieving exactly the same thing.

                {message:id=10684924}

                Apologies for quoting myself

                >
                Because Oracle produced the first commercially available SQL database they have many non-standard features because they were the first to implement them before the standards were agreed on. Treatment of nulls and hierarchical queries are examples.
                >

                And of course once a behavior is implemented pre-standard it is impossible to change to a new standard behavior that is mutually exclusive without breaking backward compatibility and existing applications.
                Think about the old Oracle join syntax, it was not wrong
                Correct, it was not. It was only guilty of being first and being innovative.
                but standards are importend, specially for SQL,
                Why, what did they achieve?
                so they changed it to ANSI join syntax in 9i.
                They did not, they supported the alternate ANSI syntax in addition to the original syntax. All ANSI syntax queries are converted to original syntax before execution, which has resulted in many bugs that affect ANSI syntax only.
                • 20. Re: insertion of null values in unique constraint column
                  sql_coder
                  3360 wrote:
                  I disagree, anyone who comes away from the concept of NULL thinking that NULL is equal to NULL and that all NULLS are equal is fundamentally and completely wrong about their understanding of NULL.
                  there is no natural or scientific concept with NULL values, it is only a questions of definition. everytime you have a three condition logic and have to convert it into a two condition logic you are in trouble and it is only a question of definition like pizza vs pasta. I will give you a simple example. lets asume you have 10 persons. From 7 persons you know their birthday. Now you need to group them, whoever is 40 years or older go left, the persons younger then 40 go right. Now I am asking you, what do you want to do with the 3 people you dont know their birthday, left or right ? and dont just say right or left, I want a scientific concept why left is correct or wrong.
                  3360 wrote:
                  It must be nice to live in an idealistic world with committees that work for the benefit of all and no doubt world peace is achievable.
                  I am not an idealist, i am a realist. I mean there is a difference between world wide peace and ANSI standards ^^
                  3360 wrote:
                  but standards are importend, specially for SQL,
                  Why, what did they achieve?
                  I dont know if you work with other dbms besides oracle, but imagine every dbms would have their own languages to manage the data in their system or even more worse, oracle would change their language complitly with every version. standards are importend not only for dbms, let us take the keyboard for example. Imagine everytime when we have a new modell, the letters on our computer keyboard would be on different locations. I dont want that and I am sure most other dont want it either......But I said I am a realist, of course the wrong standards at the wrong place can be bad, it depends on the topic you have. We can face another topic where a standard behavior in the context of rdbms would be good, how you handle empty strings ''. For oracle it is a NULL value, for most other rdbms it is not a NULL value. Of course I have my personal point of view about it (dont want to start a discussion about it), but what is most importend for me, if all rdbms would handle it the same way. it would make my daily work more easy.

                  ikrischer
                  • 21. Re: insertion of null values in unique constraint column
                    6363
                    Ikrischer wrote:
                    3360 wrote:
                    I disagree, anyone who comes away from the concept of NULL thinking that NULL is equal to NULL and that all NULLS are equal is fundamentally and completely wrong about their understanding of NULL.
                    there is no natural or scientific concept with NULL values, it is only a questions of definition. everytime you have a three condition logic and have to convert it into a two condition logic you are in trouble and it is only a question of definition like pizza vs pasta.
                    It is not a personal preference or opinion that it is clearly incorrect to treat all values with a NULL state as equal. You are simply wrong.
                    3360 wrote:
                    It must be nice to live in an idealistic world with committees that work for the benefit of all and no doubt world peace is achievable.
                    I am not an idealist, i am a realist. I mean there is a difference between world wide peace and ANSI standards ^^
                    3360 wrote:
                    but standards are importend, specially for SQL,
                    Why, what did they achieve?
                    I dont know if you work with other dbms besides oracle, but imagine every dbms would have their own languages to manage the data in their system or even more worse,
                    They do anyway. And even if you were to come up with a query using only a subset of syntax that all databases support, there is no guarantee that the results will be the same.
                    oracle would change their language complitly with every version.
                    This statement is just as stupid as the claim that NULL should be treated as equal to NULL.
                    standards are importend not only for dbms, let us take the keyboard for example.
                    Oh please, databases are not in any way comparable to keyboards and typewriters, which do feature different layouts in different countries. There are no standards defining keyboard layout, anyone is free to create a different layout, and the market will decide, probably negatively, whether it will be a success. Just like the market defines what features make successful software.
                    Imagine everytime when we have a new modell, the letters on our computer keyboard would be on different locations. I dont want that and I am sure most other dont want it either......
                    So you wouldn't buy it and probably others wouldn't and it wouldn't sell and it would disappear.
                    We can face another topic where a standard behavior in the context of rdbms would be good, how you handle empty strings ''. For oracle it is a NULL value, for most other rdbms it is not a NULL value.
                    Yes Oracle has no separate concept of NULL and empty string, just like Oracle and all other databases have no separate concepts for NULL and empty numbers, or NULL and empty dates, or any other data type, so Oracle is the only database that is logically consistent in this case.
                    Of course I have my personal point of view about it (dont want to start a discussion about it), but what is most importend for me, if all rdbms would handle it the same way. it would make my daily work more easy.
                    I am sure that one individuals life being easier, because they can write the same additional code to differentiate between empty strings and NULL strings, but not empty numbers and NULL numbers, etc, on all databases would be a great comfort to everyone else whose simpler and more logical code stops working at that point.

                    Do you actually believe it would be possible for Oracle to change their more logical treatment of NULL values to match the broken standard implementation and not care about all the existing applications it would break in the process just to make people like you who believe that all databases should be exactly the same and have no differentiating features happier?
                    But I said I am a realist
                    I believe you are using the word realist with a competently different meaning than the accepted definition, in a similar way to your use of the words NULL, preference, opinion and science etc.

                    Given your stated goals a realist would choose a single, preferably free database that is as ANSI compliant as they require, develop only on that and give it away with their applications and quit complaining that other databases have different features and do things differently.

                    It would probably make us all happier.
                    • 22. Re: insertion of null values in unique constraint column
                      sql_coder
                      3360 wrote:
                      It is not a personal preference or opinion that it is clearly incorrect to treat all values with a NULL state as equal. You are simply wrong.
                      ok, lets bring it down to a few facts or we will have hundreds of quotes, I asked you a question about the 10 people and to group them by their birthday. If NULL is logical everytime and based on a scientific concept, please demonstrate it for us with examples.
                      3360 wrote:
                      There are no standards defining keyboard layout
                      http://en.wikipedia.org/wiki/ISO/IEC_9995

                      ikrischer
                      • 23. Re: insertion of null values in unique constraint column
                        6363
                        Ikrischer wrote:
                        3360 wrote:
                        It is not a personal preference or opinion that it is clearly incorrect to treat all values with a NULL state as equal. You are simply wrong.
                        ok, lets bring it down to a few facts or we will have hundreds of quotes, I asked you a question about the 10 people and to group them by their birthday. If NULL is logical everytime and based on a scientific concept, please demonstrate it for us with examples.
                        In your example it is incorrect to put the people on either the right or the left if you do not know their birthday and that is the categorization requirement. Just as it is wrong to say all of them should be on the same side which it appears is what you are claiming.

                        >
                        3360 wrote:
                        There are no standards defining keyboard layout
                        http://en.wikipedia.org/wiki/ISO/IEC_9995
                        It does not define specific layouts but provides the base for national and industry standards which define such layouts.
                        >

                        Yes, I knew after I posted that the statement was too broad. There is no standard defining a specific layout and there is nothing to prevent the creation and sale of keyboard layouts that do not even adhere to the loose standards that do exits.

                        There was a thread recently about someone not wanting to use regular expressions in Oracle because they are not part of the ANSI standard. They are in fact part of the ANSI standard, but the standard does not go as far as to define the exact syntax to be used, so regular expressions are both ANSI standard and non-portable between different database implementations of the standard.
                        • 24. Re: insertion of null values in unique constraint column
                          padders
                          http://en.wikipedia.org/wiki/ISO/IEC_9995

                          Whoever knew keyboards were so much fun!
                          • 25. Re: insertion of null values in unique constraint column
                            6363
                            Many years ago I developed a hatred of the French keyboard layout, primarily because you need to use Shift+key to get numbers

                            http://en.wikipedia.org/wiki/Keyboard_layout#French

                            If there was an ANSI standard keyboard, it would look like this.

                            http://3-ps.googleusercontent.com/h/www.daskeyboard.com/images/products/ultimate/555x276xmechanical_keyboard.jpg.pagespeed.ic.75XENKpLec.jpg

                            Either that or have half a novel inscribed on each key.
                            • 26. Re: insertion of null values in unique constraint column
                              sql_coder
                              3360 wrote:
                              Ikrischer wrote:
                              3360 wrote:
                              It is not a personal preference or opinion that it is clearly incorrect to treat all values with a NULL state as equal. You are simply wrong.
                              ok, lets bring it down to a few facts or we will have hundreds of quotes, I asked you a question about the 10 people and to group them by their birthday. If NULL is logical everytime and based on a scientific concept, please demonstrate it for us with examples.
                              In your example it is incorrect to put the people on either the right or the left if you do not know their birthday and that is the categorization requirement. Just as it is wrong to say all of them should be on the same side which it appears is what you are claiming.
                              I agree, left or right, both would be wrong, but that is exacty what the combined unique constraint is doing. the constraint has only two options, fire or dont fire. there is no option for dont know or fire only a little bit. there is no logic what to do with the 3 persons when you have only left an right. it is exacly the same problem with the unique constraint, give an error message or not. but show me our magical logic when you have two options, still waiting for it, left or right.....

                              ikrischer
                              • 27. Re: insertion of null values in unique constraint column
                                6363
                                Ikrischer wrote:
                                3360 wrote:
                                Ikrischer wrote:
                                3360 wrote:
                                It is not a personal preference or opinion that it is clearly incorrect to treat all values with a NULL state as equal. You are simply wrong.
                                ok, lets bring it down to a few facts or we will have hundreds of quotes, I asked you a question about the 10 people and to group them by their birthday. If NULL is logical everytime and based on a scientific concept, please demonstrate it for us with examples.
                                In your example it is incorrect to put the people on either the right or the left if you do not know their birthday and that is the categorization requirement. Just as it is wrong to say all of them should be on the same side which it appears is what you are claiming.
                                I agree, left or right, both would be wrong, but that is exacty what the combined unique constraint is doing. the constraint has only two options, fire or dont fire. there is no option for dont know or fire only a little bit. there is no logic what to do with the 3 persons when you have only left an right. it is exacly the same problem with the unique constraint, give an error message or not. but show me our magical logic when you have two options, still waiting for it, left or right.....
                                I already said the combined unique constraint with null behavior was incorrect, if practical in, in Oracle.

                                {message:id=10758010}
                                3360 wrote:
                                Ikrischer wrote:
                                and yes NULL is strange in ORACLE, by default other dbms wont allow more then one NULL value on a column with a unique constraint.
                                So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
                                For a single value index to allow only one NULL, it is equating NULL equal to NULL which is clearly incorrect.
                                • 28. Re: insertion of null values in unique constraint column
                                  sql_coder
                                  3360 wrote:
                                  I already said the combined unique constraint with null behavior was incorrect, if practical in, in Oracle.
                                  this is not pro or contra oracle, it is about NULL in general, cause all dbms will face the problem. you said to handle NULL is always logical and that is not true, still you fail to show that logic. if you say oracle is doing it wrong, show us the correct and logical way to handle NULL values in every situation, when you have to convert a three condition logic into a two condition logic. I told you it is like pasta or pizza and the only good way is to handle it via ANSI, cause it is only a question of definition. if you still think different, then show us the logic, what is correct, left or right, fire the constraint or not.....

                                  ikrischer

                                  Edited by: Ikrischer on Dec 21, 2012 5:36 PM
                                  • 29. Re: insertion of null values in unique constraint column
                                    6363
                                    Ikrischer wrote:
                                    3360 wrote:
                                    I already said the combined unique constraint with null behavior was incorrect, if practical in, in Oracle.
                                    this is not pro or contra oracle, it is about NULL in general, cause all dbms will face the problem. you said to handle NULL is always logical and that is not true,
                                    No I didn't say that, you are making it up. It is very easy to make up things that someone did not say and then argue it is not true.
                                    still you fail to show that logic.
                                    You mean the logic that I didn't claim existed?
                                    if you say oracle is doing it wrong,
                                    I was agreeing with you when you said Oracle was doing it wrong.
                                    that show us the correct and logical way to handle NULL values in every situation,
                                    This is a very immature statement.
                                    I told you it is like pasta or pizza and the only good way is to handle it via ANSI, cause it is only a question of definition.
                                    Leaving aside the mess it would leave the Italian food industry in if ANSI got involved. ANSI came up with the definition of NULL in this instance after Oracle already had an implementation. The contradictory ANSI definition was not an improvement on the Oracle behavior, and would have forced Oracle as a software maker to break every existing application that developers had already developed on it's database.

                                    That would have been a very bad thing for Oracle to do then, and it would be even worse now.
                                    if you still think different, then show us the logic, what is correct, left or right, fire the constraint or not.....
                                    NULL is not equal to NULL. Databases that implement constraints contrary to this are completely wrong, whether they are ANSI compliant or not in this instance.

                                    Oracle, despite its differences, is ANSI compliant whether you like or not.

                                    If you don't like it you should only use databases that do meet your tastes, leave Oracle alone and stop whining, because it is not going to change, I guarantee, and certainly not just to make you happy.