insert to database when table doesn't have primary key (many-to-many) c#

Question

I want to insert data in database. I have three table (Recipe, RecipeDrug and Drug) and relationship many-to-many. I don't know how to insert when don't have primary key in associated table. This is my code for associated table:

class RecipeDrug
{
    public Recipe Recipe { get; set; }
    public Drug Drug{ get; set; }
    public int Quantity { get; set; }
    public string Remark{ get; set; }

    //insert to database
    public int Save()
    {
        string sqlQuery = "INSERT INTO Recipe_Drug (id_recipe, id_drug, quantity, remark) VALUES ('" + Recipe.IdRecipe + "', '" + Drug.IdDrug + "', '" + Quantity + "', " + Remark + ")";
        return DB.Instance.ExecuteQuery(sqlQuery );
    }
}

id_recipe and id_drug is forigen key. I don't know how to connect this table with other two.

Answer 1

You cannot insert a values in a column that has a foreign key to another table.

There are multiple ways to do it, here are some:

1- Place a trigger on [Recipe_Drug] that would insert that value (for example [id_recipe] = 'abc') into table [Recipe] then retrieves the id/primary key to insert into [Recipe_Drug].

2- Create a view that joins those id columns back to their original values, then you can insert into the view and it will do the step above for you.

On a side note, I highly recommend researching about SQL Injection as it could save you a world of trouble.

https://www.w3schools.com/sql/sql_injection.asp

http://bobby-tables.com/

Answer 2

A table does not require a primary key to be inserted into. The SQL script as written will function without issue. That said, the return type of your method looks like you are expecting to return an ID. You really have two options in this case:

1. Change the return type of your method to void, or bool (if you want to at least indicate whether or not the record was inserted successfully). I don't know how feasible this is since I do not know kind of object DB is, or its ExecuteQuery method works.
2. Add a surrogate key (with "identity" set). If the combinations of id_recipe and id_drug need to be unique (ie. id_recipe=1 + id_drug=1 should only appear once), then you'll want to add a unique constraint on those two columns as well.

I would be remiss to not point out the SQL injection issues many others have noted as well. You should convert your query to use parameterized inputs, otherwise it's a trivial matter to hack your database with malformed input.

Answer 3

There were 2 things I noticed in your SQL Statement, the previously mentioned lack of parameters and you had all of the inserted values single quoted- not needed for integers. Using parameters takes care of any wrapping that is needed.

The ExecuteQuery() in JDBC would be for a SELECT query. Don't know what provider you are using, so cannot tell what this method does or returns.

Sql Server would generally use ADO, so that is what I have written this in. You would use ExecuteNonQuery() which returns an INT representing Rows Affected. I have parameterized the query to remove the risks of Sql Injection. Notice that the catch sets the ReturnValue to -1 so you would know if there was a failure in the method.

This table appears to be a bridge table and does not need to have a Primary Key. Due to the Foreign Key constraints, the IDs for Recipe & Drug would need to exist in their respective tables. Please note that Foreign Keys in most cases don not increase performance and are only for _Referential Integrity_; to see a performance gain you would need to have those columns indexed in this table. You could do this as a Compound Key with the two values. If you choose to make this a unique index you would need to make sure on updates that you pre-check this key to avoid a conflict.

@Eric mentioned in their answer that you may want to change the method type to void or bool. If you were to do this, I would go with bool, setting to true on success [ ReturnValue = (cmd.ExecuteNonQuery() ==1); ] and remove the portion in the catch, so the error would domino back to the caller.

public int Save() {

    int ReturnValue;
    string sqlQuery = "INSERT INTO Recipe_Drug (id_recipe, id_drug, quantity, remark) VALUES (@idRecipe, @idDrug, @quantity, @remark)";

    using (SqlConnection conn = new SqlConnection(ConnectionString)) {
        using (SqlCommand cmd = new SqlCommand(sqlQuery, conn)) {
            cmd.Paramaters.AddWithValue("@idRecipe", Recipe.IdRecipe);
            cmd.Paramaters.AddWithValue("@idDrug", Drug.IdDrug);
            cmd.Paramaters.AddWithValue("@quantity", Quantity);
            cmd.Paramaters.AddWithValue("@remark", Remark);

            try {
                conn.Open();
                ReturnValue = cmd.ExecuteNonQuery();
            }
            catch(Exception ex) {
                ReturnValue = -1;
                // your error handling
            }
            finally { conn.Close(); }
        }
    }
    return ReturnValue;
}