-
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
-
Post the table structure, and script that created the table.
-
create table aaa(
a varchar2(10) NOT NULL,
b number);
SQL> desc aaa
Name Null? Type
------------------------------- -------- ----------------------------
A NOT NULL VARCHAR2(10)
B NUMBER
-
Where are the null values?
Can you do a select on the table?
MH
I remember when this place was cool.
-
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 ?
-
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.
-
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
-
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) = ''
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|