How do I programmatically create two foreign keys in mysql table

Question

I am trying to create two foreign keys in a mysql (SQLLITE) table and I am getting the following error:

  'SQL logic error or missing database near "id_value": syntax error'

Here is the error with the create table sql string:

  'Exception in 'Create_Table_History' for CREATE TABLE e2pHistory (userID nvarchar(100), timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, e2pType int, id_field nvarchar(100), FOREIGN KEY(id_field) REFERENCES e2pField(f_indx), id_value nvarchar(100), FOREIGN KEY(id_value) REFERENCES e2pValues(v_indx)) : SQL logic error or missing database near "id_value": syntax error'

Here is the code that generates the Error:

    private void Create_Table_History()
    {
        string col1 = string.Format("{0} nvarchar(100)", e2pHistory_Column_UserID);

        string col2 = string.Format("{0} TIMESTAMP DEFAULT CURRENT_TIMESTAMP", e2pHistory_Column_TimeStamp);

        string col3 = string.Format("{0} int", e2pHistory_Column_Type);

        string col4 = string.Format("{0} nvarchar(100) FOREIGN KEY({0}) REFERENCES {1}({2})", e2pHistory_Column_ID_Field, e2PField_Table, e2pField_Column_Indx);

        string col5 = string.Format("{0} nvarchar(100) FOREIGN KEY({0}) REFERENCES {1}({2})", e2pHistory_Column_ID_Value, e2PValue_Table, e2pValue_Column_Indx);


        string sql = string.Format("CREATE TABLE {0} ({1}, {2}, {3}, {4}, {5})", e2pHistory_Table, col1, col2, col3, col4, col5 );

        _ExecuteNonQuery("Create_Table_History", sql);
    }

NOTE: the table creation works IF I remove the 'col5' from the sql string as follows, ( but I need the second foreign key ! )

  string sql = string.Format("CREATE TABLE {0} ({1}, {2}, {3}, {4})", e2pHistory_Table, col1, col2, col3, col4 );

Here is the code that sets the value of the strings used in the above code

        e2pHistory_Table = "e2pHistory";
        e2pHistory_Column_UserID = "userID";
        e2pHistory_Column_TimeStamp = "timeStamp";
        e2pHistory_Column_Type = "e2pType";
        e2pHistory_Column_ID_Field = "id_field";
        e2pHistory_Column_ID_Value = "id_value";

        e2PField_Table = "e2pField";
        e2pField_Column_Indx = "f_indx";

        e2PValue_Table = "e2pValues";
        e2pValue_Column_Indx = "v_indx";
        e2pValue_Column_Value = "v_value";
        e2pValue_Column_TimeStamp = "v_timeStamp";

Here is the code that executes the SQL string:

    protected void _ExecuteNonQuery(string function, string sql)
    {
        using (SQLiteConnection conn = GetConnection())
        {
            conn.Open();
            using (SQLiteCommand command = new SQLiteCommand(sql, conn))
            {
                command.ExecuteNonQuery();
            }
            conn.Close();
        }
    }

    protected SQLiteConnection GetConnection()
    {
        string conString = string.Format("Data Source={0};Version=3;foreign keys=true;", fileName);
        SQLiteConnection conn = new SQLiteConnection(conString);
        return conn;
    }

note: fileName is set to the full path and name of the mysql db file

   C:\\temp\\mydb

I thought about the fact that the referenced tables must be created before the table with the foreign key, here is the code that creates all the tables:

    protected override void Create_Tables()
    {
        Create_Table_Field();
        Create_Table_Values();
        Create_Table_History();
    }

Here is the code that creates the 'Field' table:

    private void Create_Table_Field()
    {
        string col1 = string.Format("{0} nvarchar(100) PRIMARY KEY", e2pField_Column_Indx);

        string sql = string.Format("CREATE TABLE {0} ({1})", e2PField_Table, col1 );

        _ExecuteNonQuery("Create_Table_Field", sql);
    }

Here is the code that creates the 'Value' table:

    private void Create_Table_Values()
    {
        string col1 = string.Format("{0} nvarchar(100) PRIMARY KEY", e2pValue_Column_Indx);
        string col2 = string.Format("{0} nvarchar(100)", e2pValue_Column_Value);
        string col3 = string.Format("{0} TIMESTAMP DEFAULT CURRENT_TIMESTAMP", e2pValue_Column_TimeStamp );

        string sql = string.Format("CREATE TABLE {0} ({1}, {2}, {3})", e2PValue_Table, col1, col2, col3);

        _ExecuteNonQuery("Create_Table_Values", sql);
    }

Answer 1

Foreign keys must be created AFTER the columns in the Create Table syntax

        CREATE TABLE e2pHistory (
        userID nvarchar(36), 
        timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
        e2pType int, 
        fk_f_indx nvarchar(36), 
        fk_v_indx nvarchar(36), 
        FOREIGN KEY (fk_f_indx) REFERENCES e2pField (f_indx), 
        FOREIGN KEY (fk_v_indx) REFERENCES e2pValues (v_indx)