0

I am working on an App connecting to the Snowflake database. I stumbled upon an issue while loading CSV files. I was using the NULL_IF condition in copy command to change column value to null if any empty value is encountered during the load

On investigation, I came to know that one of the columns is part of the composite primary key, and the value for this column was empty in a few rows. after I removed the NULL_IF condition, It started working fine.

Why empty values are allowed but not null values in composite primary keys?

I searched a lot, but all the answers are trying to explain why a composite key column can not have null values and it somewhat makes sense. But then why empty value is considered legit? Can somebody please explain? Thanks

example:

CREATE TABLE table_employee (
    column1 Varchar2(255),
    column2 Varchar2(255),
    column3 Varchar2(255),
    primary key (column1, column2)
   ....
);

Following Insert will succeed:

INSERT INTO table_employee(column1, column2, column3, ...)
VALUES ('', 'abc', '', ...);

Following Insert will fail:

INSERT INTO table_employee(column1, column2, column3, ...)
VALUES (null, 'abc', '', ...);
Ish Mahajan
  • 78
  • 9
  • "why a composite key column can not have null values and it somewhat makes sense". If that makes sense to you, you should also understand why `''` *is* allowed (I assume you mean empty string when you say empty values). `''` Is a value, `null` is not – HoneyBadger Apr 29 '22 at 12:03
  • Does this answer your question? [What to do with null values when modeling and normalizing?](https://stackoverflow.com/questions/40733477/what-to-do-with-null-values-when-modeling-and-normalizing) – philipxy Apr 29 '22 at 12:09
  • "empty" is not clear. A row & column cell with a null is not "empty". Null is not the empty string in standard SQL. (It's a default in Oracle SQL.) PS Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Apr 29 '22 at 12:13
  • @philipxy this doesn't answer what I am looking for. please re-read my query. I had already mentioned in my question that there are so many articles explaining why NULL can't be inserted in the composite key. let me put my question this way. why different treatment for empty value as compared to null? in my opinion, if null is not allowed in the composite key, Neither should "Empty" value be. – Ish Mahajan May 01 '22 at 14:47
  • Please clarify via edits, not comments. PS "Why" is usually a bad & vague way to phrase a question. If you want to know why your expectations are not met in terms of the language definition, you need to give your expectations & why--and to have done reasonable research, with justification per authoritative documentation; and you are really what the language is defined to be. But you could just as well be asking why the language designers decided to include a given feature. (Eventually you did give code.) PS Say "the empty string", "empty" is unclear. – philipxy May 02 '22 at 04:38

1 Answers1

1

why empty value is considered legit

Empty Strings and NULL Values

An empty string is a string with zero length or no characters, whereas NULL values represent an absence of data.

Thus an empty string is conceptually different than NULL.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    This is what I was looking for. thanks @Lukasz. I didn't think about nulls in this way before. good article – Ish Mahajan May 01 '22 at 14:50
  • @IshMahajan If you think one answer post has been most helpful then please click on the check mark to "accept". That is how askers "thank" on [so]. [Help] – philipxy May 10 '22 at 07:59