why empty values are allowed but not null values in composite primary keys

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)
   ....
);

​x
 
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', '', ...);

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

Following Insert will fail:

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

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

Answer

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.

Advertisement

Answer