DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Null values in table

  1. #1
    Join Date
    Apr 2001
    Posts
    18

    Null values in table

    Hello,
    I created a table with one of the columns having "NOT NULL" clause.
    The table now has a row with a null value in that specific column.

    Hows is this possible? Is there a way we can force a table to have a null column inspite of the NOT NULL constraint? How did this row enter the table to begin with ?

    Thanks
    S

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Post the table structure, and script that created the table.

  3. #3
    Join Date
    Apr 2001
    Posts
    18
    create table aaa(
    a varchar2(10) NOT NULL,
    b number);

    SQL> desc aaa
    Name Null? Type
    ------------------------------- -------- ----------------------------
    A NOT NULL VARCHAR2(10)
    B NUMBER

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    Where are the null values?
    Can you do a select on the table?

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Apr 2001
    Posts
    18
    Yes, when I do a select, there are about 10 valid rows and there is one row with column "a" has null and column b has a zero in it.

    Is there a way one row might have got into it somehow ?

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    While it should be impossible to have a null value in that column, you can have a value of one or more spaces.

    Try this.

    SELECT ASCII(SUBSTR(a, 1, 1))
    FROM aaa
    WHERE RTRIM(a) = ''
    /

    If it is a space the value returned will be 32. It could also get populated with a tab. You may want to find the constraint for this and make sure it is enabled.

  7. #7
    Join Date
    Apr 2001
    Posts
    18
    Thanks for your response. great suggestion.
    I tried the little test .....
    I got no rows returned. So, there are no spaces in the column.
    Any other suggestions ?

    Thanks for your help

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by gandolf989
    While it should be impossible to have a null value in that column, you can have a value of one or more spaces.

    Try this.

    SELECT ASCII(SUBSTR(a, 1, 1))
    FROM aaa
    WHERE RTRIM(a) = ''
    /

    If it is a space the value returned will be 32. It could also get populated with a tab. You may want to find the constraint for this and make sure it is enabled.
    That's a single quote, space, single quote, not a regular quotation mark. The font on the forum hides that.

    Code:
    SELECT ASCII(SUBSTR(a, 1, 1)) 
    FROM aaa
    WHERE RTRIM(a) = ''

  9. #9
    Join Date
    Apr 2001
    Posts
    18
    thanks for the clarification Steven.
    Yes, I tried single quote, space, single quote.
    It returned no rows. So, its safe to say that its not space.

  10. #10
    Join Date
    Dec 2000
    Posts
    126
    you can try this sql to find out row with non-printable character
    SELECT ASCII(SUBSTR(a, 1, 1))
    FROM aaa
    WHERE ASCII(SUBSTR(a, 1, 1)) < 48


    note: chr(48) = A

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