Foreign Key Constraint

[aguest1005]

I'm inserting data into a SQL database with a foreign key on the table.

The input script takes from a staging table to update a live table with new records.

The input tool uses this query to retrieve the data

select c.AbsenceStartDate,
c.AbsenceEndDate,
c.AbsenceDays,
c.AbsenceHours,
c.AbsenceReasonCode,
c.AbsenceReasonLDesc,
c.AbsenceCertTypeLDesc,
c.AbsenceTypeCode,
c.AbsenceEntryStatus,
c.AbsenceEntryStatusDesc,
c.EmployeeID,
c.CreationDate,
c.ModifiedDate

From (select a.* , b.EmployeeID
from [HR].[StagingAbsence] a
Join HR.Employee b on a.EmployeeNumber = b.EmployeeNumber) c
Where not Exists (Select EmployeeID, AbsenceStartDate, AbsenceEndDate
From HR.Absence d
Where c. EmployeeID = d.EmployeeID
and c.AbsenceStartDate = d.AbsenceStartDate
and c.AbsenceEndDate = d.AbsenceEndDate)

Where EmployeeID is the foreignKey.

The append Existing on the Output tool is giving me the foreignkey constraitn error. 

Is there a way to get around this? Better way of achieving this?

[ChrisTX]

What is the EmployeeID data type for in Alteryx just before the upload to the SQL database, and what is the EmployeeID data type in your SQL table?  An Alteryx data type of Double could cause issues.

If the data type is not Double, please explain more of your process.  Before you upload to the table that has the foreign key requirement, I'm guessing a previous step in your process would have been completed to upload any EmployeeIDs that didn't already exist to the related table?

Chris

[aguest1005]

There are only two tools in the flow. Input tool which is based around the above sql code and then the output tool that updates the table. I don't carry out any further manipulations.

The datatype of the EmployeeID from the input is Int32 and the SQL Table it's going into is an int.

These are the setting on the output tool

[ChrisTX · Accepted solution]

I don't think this is an Alteryx question.  More like an SQL question.

More research...

  for the data you want to upload, do you expect all of the EmployeeIDs to already exist in the table referenced by the foreign key constraint, or do you know that you may be uploading new EmployeeIDs?

  Use a Join tool to compare your list of unique EmployeeIDs to the table referenced by the foreign key constraint

If your process may be uploading new EmployeeIDs and you are uploading to a table that has a foreign key constraint, you must first upload any missing EmployeeIDs to the related table.

Example:

   table A = the "primary" table you're uploading to

   table B = the related table referenced by the foreign key constraint

In table A, if EmployeeID has a foreign key constraint referencing table B, all EmployeeIDs in your upload file must first exist in table B.

You'll need a multi-step process.

1) Start with table A. Get a unique list of EmployeeIDs.  In Alteryx use the Group By or Unique tool.

2) join to the table referenced in the foreign key constraint, identify all EmployeeIDs from your upload that don't already exist in the related table.  In Alteryx, use the Join tool with Left input anchor = result of step 1 above. Right input anchor = Table B.  Join on field EmployeeID.  Take the Left output anchor, then upload the EmployeeIDs from that data stream to table B.

3) Then you should be able to successfully upload your new records to table A.

Chris

[aguest1005]

I went back and looked at th SQL table, the foreign key was created on the wrong ID in the table. I've fixed this and all worked fine. 
Thank you for you help.