Hi all,

I get this error when inserting data...

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Participant_Log_BiometricInstance_Participant_Activities". The conflict occurred in database "ProvantCustomerPortal", table "dbo.Activities", column 'Id'.
The statement has been terminated.

My query looks like this 

insert into [dbo].[Participant_BiometricInstance](ParticipantId, ActivityId, ProviderTypeId, Fasting, ExternalSystemId, ResultsDate, ModifiedBy, ModifiedDate)
select participantID,'','','',NULL,getdate(),NULL,getdate() from [dbo].[Participant_Profile]
union all 
select '',id,'','','','','','' from [dbo].[Activities]
union all
select '','',id,'','','','','' from [dbo].[Log_ProviderType]

ParticipantID, ActivityId, ProviderTypeId are Foreign Keys.

Id is primary key in Activity table

Id is primary key in Log_ProviderType.

Does anyone know how can we solve this?

I'll just make a bunch of assumptions here in an attempt to point you in the right direction.

It appears that the problem is with your first select statment...

select participantID,'','','',NULL,getdate(),NULL,getdate() from [dbo].[Participant_Profile]

I'm assuming you don't have a record in your Activity table with an ID of ''.  Your first select statement would cause a record to be added to the [Participant_BiometricInstance] with an ActivityID of '' assuming there are records in the [Participant_Profile] table.

• Proposed as answer by Ed at Hammar Technology Tuesday, December 03, 2013 9:40 PM

i have data in Activity Table with an ID column. 

Id
2
3
4
5
8

As the error states, you cannot have values in FK table, if it does not exist in Primary Key Table.

Do you have '' - value in your primary key - which would be Activity, log provider and participant profile table.

To fix this, instead of '' use NULL, assuming your FK columns are NULLable.

Thanks for your suggestion

There are values in Activity,Log provider and participant profile tables. I used NULL instead of '' 

Still it doesn't work? :(

Does your FK Columns allow NULL??What is your error this time??

Please post your table's DDL and the error you are getting for better understanding on your issue. 

FK column doesn't allow NULL values. I tried using NULL instead of ''.

But it shows the same error.

I bet it would be complaining about NULL values inserted to column which doesnt allow NULLs this time. Make the column as NULL and then it should work fine

ALTER TABLE [dbo].[Participant_BiometricInstance] ALTER COLUMN  ActivityId <yourdatatype> NULL

Can i write my query like this??

insert into [dbo].[Participant_BiometricInstance](ParticipantId, ActivityId, ProviderTypeId, Fasting, ExternalSystemId, ResultsDate, ModifiedBy, ModifiedDate)
select participantID,'','','',NULL,getdate(),NULL,getdate() from [dbo].[Participant_Profile]
union all 
select '',id,'','','','','','' from [dbo].[Activities]
union all
select '','',id,'','','','','' from [dbo].[Log_ProviderType]

is this correct? 

will you suggest me any other appropriate way to write this query??

FK column doesn't allow NULL values. I tried using NULL instead of ''.

But it shows the same error.

Hello ShyamReddy,
 
   As you stated above, if your columns does not allow nulls and if you try to insert nulls,what would you except?
   Again, FK columns can take nulls but only if they are defined as null in yout Table DDL. Also, '' and NULL are different .
   '' is a value and null is not. So, solution is either make sure your have this '' value in your primary table or make your FK columns as
   NULL. I would recommend the later though. Please post your Table DDL's for more accurate info. Without your Table's DDL , we are only but guessing the solution(which in your case is not that bad since there are only two probable solutions).

I have never quite understood the need for FK Constraint columns to allow NULLs.

Surely the intention of having a PK-FK relationship is to ensure that a record in the FK table can determine it's PK parent via some value, isn't it?

If this is a requirement, I would suggest there is something amiss in the basic DB design that needs to be addressed.

I have never quite understood the need for FK Constraint columns to allow NULLs.

Surely the intention of having a PK-FK relationship is to ensure that a record in the FK table can determine it's PK parent via some value, isn't it?

But who says that there has to be a parent? For instance, a table like OrderDetails may have a column CampaignID. But not order lines are due to campgaigns, so in many cases CampaignID is null. But if it has a value, it needs to be an existing campaign.