CODEWITHSUNDEEP
javaandroidsqliteandroid-sqliteandroid-studio-2.3
JDProwler
JDProwler

Reputation: 109

Sqlite how to create table with Foreign key

So I have searched a few questions on this topic, but I have realized that those questions are more related to whether or not you are allowed to create tables with no PKs etc. My problem is that the following code generates a an error: E/SQLiteLog: (1) near "FOREIGN": syntax error

Code:

CREATE TABLE tblPart(PartNumber [nvarchar](15) PRIMARY KEY NOT NULL,
PartDescription [nvarchar](150) NOT NULL);

CREATE TABLE tblStock(PartNumber [nvarchar](15) FOREIGN KEY REFERENCES
tblPart(PartNumber) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
StockOH [int] NOT NULL);

Executing this code in MSSQLSERVER works perfectly, but when ran in Java/Android using SQLite it generates said error.

Thank you in advance for your assistance.

EDIT:

DataHandler Class:

public myDAL(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
{
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    //region create tblPart
    sbCreatePart.append("CREATE TABLE " + TABLE_PART + "(");
    sbCreatePart.append(COLUMN_PART_PARTNR + " [nvarchar](15) PRIMARY KEY NOT NULL, ");
    sbCreatePart.append(COLUMN_PART_DESCRIPTION + " [nvarchar](150) NOT NULL)");
    //endregion

    //region create tblCurrentStock
    sbCreateCurrStock.append("CREATE TABLE " + TABLE_CURRENTSTOCK + "(");
    sbCreateCurrStock.append(COLUMN_CURRENTSTOCK_PARTNR + " [nvarchar](15) FOREIGN KEY REFERENCES "
        + TABLE_PART + "(" + COLUMN_PART_PARTNR + ") ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, ");
    sbCreateCurrStock.append(COLUMN_CURRENTSTOCK_STOCKOH + " [int] NOT NULL)");
    //endregion
    db.execSQL(sbCreatePart.toString());
    Log.d("Test", "tblPart");
    db.execSQL(sbCreateCurrStock.toString());
    Log.d("Test", "tblCurrStock");
}

public boolean TestInsert()
{
    boolean result = true;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    try
    {
        values.put(COLUMN_PART_PARTNR, "PNTest123");
        values.put(COLUMN_PART_DESCRIPTION, "Dummy Test Part");
        Log.d("Test Input", "Values Added");
        db.insert(TABLE_PART, null, values);
        Log.d("Test Input", "Insert Successful");
    }
    catch (Exception ex)
    {
        result = false;
    }
    finally
    {
        db.close();
    }
    return result;
}

Activity Class:

public void onPartsClick(View v)
{
    myDAL dal = new myDAL(this, null, null, 1);
    boolean x = dal.TestInsert();
    if(x)
        Toast.makeText(getApplicationContext(), "Success", Toast.LENGTH_SHORT).show();
}

Upvotes: 1

Views: 963

Answers (1)

dsharew
dsharew

Reputation: 10675

You have syntax error try this: 

CREATE TABLE tblPart(PartNumber nvarchar(15) PRIMARY KEY NOT NULL,
PartDescription nvarchar(150) NOT NULL);

CREATE TABLE tblStock(PartNumber nvarchar(15)  REFERENCES
tblPart(PartNumber) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
StockOH integer NOT NULL);

You dont need FOREIGN KEY if you putting the foreign key definition on the column definition.

OR try it like this:

CREATE TABLE tblStock10(PartNumber nvarchar(15),
StockOH integer NOT NULL,
FOREIGN KEY (PartNumber) REFERENCES tblPart1(PartNumber) 
  ON UPDATE SET NULL);

Upvotes: 4

Related Questions