System.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint

Question

I'm still getting this error during debug. I'm not sure what to do, because I have added the AdddressID for the Person klass.

Please help!

The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Person_ToAddress\". The conflict occurred in database \"DirectoryDatabase\", table \"dbo.Address\", column 'AddressID'

The functions that throws this error is:

    public void CreatePersonDB(ref Person person)
    {
        string CreatePerson =
            @"INSERT INTO [Person] (FirstName, MiddleName, LastName, AddressID)
                                    OUTPUT INSERTED.PersonID  
                                    VALUES (@FName, @MName, @LName, @AID)";

        using (SqlCommand cmd = new SqlCommand(CreatePerson, OpenConnection))
        {
            // Get your parameters ready                    
            cmd.Parameters.AddWithValue("@FName", person.FirstName);
            cmd.Parameters.AddWithValue("@MName", person.MiddleName);
            cmd.Parameters.AddWithValue("@LName", person.LastName);
            cmd.Parameters.AddWithValue("@AID", person.PrimaryAddress.AddressID);

             try
            {
                person.PersonID = (int)cmd.ExecuteScalar(); //Returns the identity of the new tuple/record}
            }
            catch
            {
                Console.WriteLine("Adresse ID doesn't exist, do you want to add it? [y/n]");
                ConsoleKeyInfo input = Console.ReadKey();

                if (input.Key == ConsoleKey.Y)
                {
                    //create an insert query to the dbo.Adresse the same way you did with the dbo.person.
                    CreateAddressDB();
                }
        }
    }

The database sql code for Person & Address looks like this (after editing):

CREATE TABLE Address (
AddressID      BIGINT IDENTITY(1,1) NOT NULL,
StreetName     NVARCHAR(MAX) NOT NULL,
HouseNumber    NVARCHAR(MAX) NOT NULL,

CityID         BIGINT NOT NULL,

[PersonID] NCHAR(10) NOT NULL, [PrimaryAddress] INT NOT NULL, CONSTRAINT pk_Address PRIMARY KEY CLUSTERED (AddressID), CONSTRAINT fk_Address FOREIGN KEY (CityID) REFERENCES City (CityID) ON DELETE NO ACTION ON UPDATE NO ACTION)

This is for the Address table:

CREATE TABLE Person (
PersonID       BIGINT IDENTITY(1,1) NOT NULL,
FirstName      VARCHAR(50) NOT NULL,
MiddleName     NVARCHAR(50) NOT NULL,
LastName       NVARCHAR(50) NOT NULL,

AddressID      BIGINT NOT NULL,

CONSTRAINT pk_Person PRIMARY KEY CLUSTERED (PersonID), CONSTRAINT fk_Person FOREIGN KEY (AddressID) REFERENCES Address (AddressID) )

Possible duplicate of INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server — Anas Alweish
– Anas Alweish, Commented Oct 11, 2018 at 11:00
I read this thread, but I couldnt figured out in order to fix my code. — user10489212
– user10489212, Commented Oct 11, 2018 at 11:06

Gavenko Sergey · Accepted Answer · 2018-10-11 10:52:50Z

2

In table dbo.Address doesn`t exists record with your person.PrimaryAddress.AddressID value

answered Oct 11, 2018 at 10:52

Gavenko Sergey

311 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Adam B Over a year ago

please add a bit more detail to your answer for future readers visiting this page.

Audwin Oyong · Accepted Answer · 2024-01-23 23:54:58Z

0

You are trying to insert an AddressID to the Person table that doesn't exist in the Address table.

Try this instead:

public void CreatePersonDB(ref Person person)
{
    string CreatePerson =
        @"INSERT INTO [Person] (FirstName, MiddleName, LastName, AddressID)
                                OUTPUT INSERTED.PersonID  
                                VALUES (@FName, @MName, @LName, @AID)";

    using (SqlCommand cmd = new SqlCommand(CreatePerson, OpenConnection))
    {
        // Get your parameters ready                    
        cmd.Parameters.AddWithValue("@FName", person.FirstName);
        cmd.Parameters.AddWithValue("@MName", person.MiddleName);
        cmd.Parameters.AddWithValue("@LName", person.LastName);
        cmd.Parameters.AddWithValue("@AID", person.PrimaryAddress.AddressID);

        try()
        {
            person.PersonID = (int)cmd.ExecuteScalar();   // Returns the identity of the new tuple/record}
        catch()
        {
             DialogResult dialogResult = MessageBox.Show("Adresse ID doesn't exist, do you want to add it?", "Alerte",MessageBoxButtons.YesNo);

             if(dialogResult == DialogResult.Yes)
             {
                 // create an insert query to the dbo.Adresse the same way you did with the dbo.person.
             }
        }
    }
}

edited Jan 23, 2024 at 23:54

Audwin Oyong

2,5273 gold badges21 silver badges37 bronze badges

answered Oct 11, 2018 at 10:53

Saber CHETIOUI

502 silver badges13 bronze badges

4 Comments

user10489212 Over a year ago

So I have to add PrimaryAddress as well as AddressID to the dbo.Address?

Saber CHETIOUI Over a year ago

Yes, try to catch that exception in order to allow you to create a new AdressID record to the dbo.Adresse and then continue inserting to the dbo.Person.

user10489212 Over a year ago

Thanks @Saber, I have updated the question with changes you suggested, but i still get errors as the function is not recognizable.

Saber CHETIOUI Over a year ago

you already solved it in your first comment as you said, start inserting AdresseID in the dbo.Adresse table and then to the dbo.Person, what I proposed after may requires function that doesn't work in console application, if it still does'nt work, send more details including dbo.person and dbo.adresse actual records

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related