Why the foreign key doesn't work between C# and MySql?

Question

They have relation between AuthorID and AID

I want to add attributes to 2 tables at the same time

'Cannot add or update a child row: a foreign key constraint fails (bibliographydb10.book, CONSTRAINT book_ibfk_1 FOREIGN KEY (AuthorID) REFERENCES author (AID))'

public static bool AddNewPackage(BookCitation package)
    {
        MySqlConnection connection = DataAccess.getConnection();

        string insertStatement =
            @"INSERT into Book (
                AuthorName,
                PubYear, 
                BookTitle, 
                PubCity,
                PubName,
                PageNumbers) 
                VALUES (
                @AuthorName,
                @PubYear, 
                @BookTitle, 
                @PubCity,
                @PubName,
                @PageNumbers);
                INSERT into Author (
                AID,
                AuthorNamee,
                Affiliation)
                VALUES (@AID, @AuthorNamee, @Affiliation)";
        MySqlCommand insertCommand = new MySqlCommand(insertStatement, connection);
        insertCommand.Parameters.AddWithValue("@AID", package.AID);
        insertCommand.Parameters.AddWithValue("@AuthorName", package.AuthorName);
        insertCommand.Parameters.AddWithValue("@AuthorNamee", package.AuthorNamee);
        insertCommand.Parameters.AddWithValue("@Affiliation", package.Affiliation);
        insertCommand.Parameters.AddWithValue("@PubYear", package.PublishYear);
        insertCommand.Parameters.AddWithValue("@BookTitle", package.BookTitle);
        insertCommand.Parameters.AddWithValue("@PubCity", package.PublisherCity);
        insertCommand.Parameters.AddWithValue("@PubName", package.Publisher);
        insertCommand.Parameters.AddWithValue("@PageNumbers", package.PageNumbers);

        try
        {
            connection.Open();
            insertCommand.ExecuteNonQuery();
            return true;
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.GetType() + ex.Message);
            return false;
        }
        finally
        {
            connection.Close();
        }

    }

public class BookCitation
{
    public int BookCID { get; set; }
    public int AID { get; set; }
    public string AuthorName { get; set; }
    public string AuthorNamee { get; set; }
    public string Affiliation { get; set; }
    public DateTime PublishYear { get; set; }
    public string BookTitle { get; set; }
    public string PublisherCity { get; set; }
    public string Publisher { get; set; }
    public string PageNumbers { get; set; }
}

How can I add AuthorID with using Foreign Key?

What value you have in `package.AID` ? Do you have `AID` column on Book table? — Chetan, Dec 29 '18 at 12:59
It's AUTO_INCREMENT. If I remove the foreign key I can add to author table with authorID but book table's AID will be zero (0). — Vorn, Dec 29 '18 at 13:05
@ChetanRanpariya I changed but I think it doesn't matter : 'Cannot add or update a child row: a foreign key constraint fails (`bibliographydb10`.`book`, CONSTRAINT `book_ibfk_1` FOREIGN KEY (`AID`) REFERENCES `author` (`AID`))' This samples if I remove the foreign key https://i.stack.imgur.com/H1CmK.jpg https://i.stack.imgur.com/Yg6N0.jpg — Vorn, Dec 29 '18 at 13:15
It matter. If you have AID column in Book table and if it is referencing Author table, then you can not insert row in book table without value in AID column. Here in your case you first need to insert row in Author table and use the AID of new author to insert row in book table. This is based on the whatever you have explained so far. I still don't know your table structure. — Chetan, Dec 29 '18 at 13:19
@ChetanRanpariya Ok then I did "AID" instead of "AuthorID" but still same error 'Cannot add or update a child row: a foreign key constraint fails (bibliographydb10.book, CONSTRAINT book_ibfk_1 FOREIGN KEY (AID) REFERENCES author (AID))' . These are table structure https://i.stack.imgur.com/Bvdkp.jpg https://i.stack.imgur.com/SlAEq.jpg — Vorn, Dec 29 '18 at 13:27
I think you did not get the point. `Here in your case you first need to insert row in Author table and use the AID of new author to insert row in book table`. You might want to read bit more about foreign key relationships in database. — Chetan, Dec 29 '18 at 13:28

score 0 · Answer 1 · answered Dec 29 '18 at 13:21

0

The error message implies the Author has to exist first. Insert the new Author, retrieve the AID for that new Author, then insert the new Book with the book's AID column set to the same value as the retrieved AID value for the new Author you just inserted

answered Dec 29 '18 at 13:21

Caius Jard

72,509
5
49
80

So you're saying you can't add attributes to 2 relation tables at the same time? – Vorn Dec 29 '18 at 13:28
It rather depends what you mean by "at the same time". Generally in database systems you have to insert to one table, then the other, in the order of parent-then-child. There might only be a few nanoseconds between these inserts, but they're definitely one at a time. If you involve a transaction then they could be conceived as happening simultaneously because the operation is atomic. On this last point, more powerful databases can do deferred constraint checking so you can perform an insert in the order of child-then-parent and the relation is only checked upon commit. MySQL can't do this – Caius Jard Dec 29 '18 at 17:27

Why the foreign key doesn't work between C# and MySql?

1 Answers1