CODEWITHSUNDEEP
c#sql-servert-sqlsql-server-2012
developer
developer

Reputation: 127

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_

I have two tables , one to one relationship i makes DetailsID of projectDetails FK in ID of projects table:

 projects: 
         ID,         //has FK With DetailsID in Details table & auto identity (1,1)
         ProjectName,
         Areas,
         PaymentSystem,
         ReceivedDate,
         PropertyClassification,
         ProjectImage

  ProjectDetails:
     DetailsID ,auto identity ( 1,1)
     ProjectDetailName,
     ProjectDetailImage

I am trying to insert new record in projects table , gives me this error at this line of code :

    con.Open();
    comm.ExecuteNonQuery(); // when execute

System.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Projects_ProjectDetails". The conflict occurred in database "AlamaarRealEstate", table "dbo.ProjectDetails", column 'DetailsID'.

and this is my stored to insert :

ALTER proc [Insert_Project]
   @Projectname                    NVARCHAR(MAX) , 
   @areas                          NVARCHAR(MAX) , 
   @Paymentsystem                  NVARCHAR(MAX) , 
   @Receiveddate                   date , 
   @Classification                 NVARCHAR(MAX) , 
   @Projectimage                   Nvarchar(MAX) 
  as

  INSERT INTO dbo.Projects
      (            
        ProjectName,
        Areas,
        PaymentSystem,
        ReceivedDate,
        PropertyClassification,
        ProjectImage                                                     
      ) 
 VALUES 
      ( 
        @Projectname ,
        @areas,
        @Paymentsystem ,
        @Receiveddate ,
        @Classification,
        @Projectimage          

      )

Upvotes: 2

Views: 10596

Answers (2)

djangojazz
djangojazz

Reputation: 13232

Without more detail your question is hard to answer. For instance in your procedure you do explicit column naming for inserts and LEAVE OUT the column you are having a problem with. So if the proc was wrong it would be bombing with your example foreign key constraint error. But you are not even listing the 'ID' field to insert into with the procedure. So this is common if you are using an 'Identity' field to self seed, but you are claiming it is a foreign key. So like others have commented, without more code to show the exact way your tables are made it's hard to guess. Here is a self extracting example you could run that shows if a column is nullable and I had a key constraint it would work. Without the exact code of the tables as well as the proc it is hard to tell. What you gave is pseudo code.

USE Tester  --just a test database I have, you can use whatever database you want
GO

IF OBJECT_ID('Projects') IS NOT NULL 
    DROP TABLE Projects

IF OBJECT_ID('ProjectDetails') IS NOT NULL 
    DROP TABLE ProjectDetails

create TABLE ProjectDetails
(
    DetailsID INT CONSTRAINT PK_DetailsId PRIMARY KEY,
    ProjectDetailName VARCHAR(32)
  )

CREATE TABLE Projects 
(
    Id INT CONSTRAINT FK_Projects_ProjectDetails FOREIGN KEY (Id) REFERENCES ProjectDetails(DetailsId),         
    ProjectName varchar(32)

)
GO

IF OBJECT_ID('Insert_Project') IS NOT NULL 
    DROP PROC Insert_Project
GO

Create proc Insert_Project
   @Projectname                    NVARCHAR(MAX) 
  as

  INSERT INTO dbo.Projects  ( ProjectName ) 
  VALUES (  @Projectname )
GO

Select *
From dbo.Projects

EXEC dbo.Insert_Project @Projectname = N'Test' -- nvarchar(max)

Select *
From dbo.Projects

Upvotes: 2

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

The question explains the answer. Referential Integrity is not maintained properly, you are trying to insert into a child table for which the master value does not exist. Please insert values to Project_details first. This will resolve your issue. If you did not what this to throw an error, just check the existence of the DetailID in Projects table before inserting.

Upvotes: 7

Related Questions