Marketing Cloud SQL - cannot insert a NULL value into a non-nullable column

Question

I'm using Marketing Cloud Connect and have synced across the Salesforce Contact object, which becomes a data extension. I have another data extension that I send related information to and I want to have the two data extensions relate on sharing the Salesforce Contact ID as a primary key, so I can combine all the fields in these two data extensions into one.

My target data extension has two columns that are not nullable - the primary key (Id, which is the Salesforce Contact ID) and Email. I'm not sending a null value to either column (I'm only sending null values to nullable columns), but I'm still getting the error message.

My query is as follows:

SELECT
c.Id,
CreatedDate,
Birthdate,
Email,
EmailBouncedDate,
EmailBouncedReason,
FirstName,
HasOptedOutOfEmail,
LastModifiedDate,
LastName,
Newsletter__c,
NewsletterJoinDate__c,
NewsletterSource__c,
Phone,
p.Active,
p.County,
p.Label,
p.Last_active_date,
p.Region,
p.State,
p.Timezone

FROM Contact_Salesforce c
INNER JOIN PROFILE_DATA p ON c.Id=p.Contact_ID
WHERE C.SyncToMarketingCloud__c = 'TRUE'

Answer 1

Assuming that all contacts in the synchronized DE have an ID, I believe the problem comes from a contact that does not have an email address.

Trying to insert an empty value into a non nullable field causes the error you're getting.

Since you're making the email field not nollable, I assume you don't need contacts that do not have an email adress. So, go and try and add another verification in your where clause:

Email IS NOT NULL 
AND Email != ''