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

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