> if you attempt to insert an empty sting ("") into a not-nullable column, it will fail

Yes, because it will convert the empty string into a NULL, and fail to insert that NULL into a non-NULLable column.