Android - SQLite - syntax error (code 1): , while compiling: CREATE TABLE

Question

By adding new items to the database I get this error:

near ")": syntax error (code 1): , while compiling: CREATE TABLE Events(name TEXT,art TEXT,tag TEXT primarymonat TEXT primaryjahr TEXT primary)

What did I do wrong? Can't find the mistake..

public class database extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "eventsManager";

// Contacts table name
private static final String TABLE_EVENTS = "Events";

// Contacts Table Columns names
private static final String KEY_NAME = "name";
private static final String KEY_ART = "art";
private static final String KEY_TAG = "tag";
private static final String KEY_MONAT = "monat";
private static final String KEY_JAHR = "jahr";

public database(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
            + KEY_NAME + " TEXT," + KEY_ART + " TEXT,"
            + KEY_TAG + " TEXT primary" + KEY_MONAT + " TEXT primary" + KEY_JAHR + " TEXT primary"+ ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);

    // Create tables again
    onCreate(db);
}

/**
 * All CRUD(Create, Read, Update, Delete) Operations
 */

// Adding new contact
void addContact(events contact) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName()); // Event Name
    values.put(KEY_ART, contact.getArt());
    values.put(KEY_TAG, contact.getTag());
    values.put(KEY_MONAT, contact.getMonat());
    values.put(KEY_JAHR, contact.getJahr());

    // Inserting Row
    db.insert(TABLE_EVENTS, null, values);
    db.close(); // Closing database connection
} }

Logcat:

    10-24 20:11:38.360    2107-2107/com.example.michael... D/AndroidRuntime﹕ Shutting down VM
10-24 20:11:38.360    2107-2107/com.example.michael... W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0x40ca3930)
10-24 20:11:38.370    2107-2107/com.example.michael... E/AndroidRuntime﹕ FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.michael.../com.example.michael...e.FullscreenActivity}: android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE Events(name TEXT,art TEXT,tag TEXT primarymonat TEXT primaryjahr TEXT primary)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
            at android.app.ActivityThread.access$600(ActivityThread.java:141)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:5041)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
            at dalvik.system.NativeStart.main(Native Method)
     Caused by: android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: CREATE TABLE Events(name TEXT,art TEXT,tag TEXT primarymonat TEXT primaryjahr TEXT primary)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
            at com.example.michael....database.onCreate(database.java:42)
            at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
            at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
            at com.example.michael....database.addContact(database.java:61)
            at com.example.michael....FullscreenActivity.onCreate(FullscreenActivity.java:149)
            at android.app.Activity.performCreate(Activity.java:5104)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
            at android.app.ActivityThread.access$600(ActivityThread.java:141)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:5041)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
            at dalvik.system.NativeStart.main(Native Method)

what is the use of the word "primary" in your sql? i have never come across it before!! — x-treme, Oct 24 '14 at 18:12
avoiding using global variables like tag use tag_column and a table has only one primary key, — Daniel Nyamasyo, Apr 17 '20 at 14:53

score 0 · Accepted Answer · answered Oct 26 '14 at 08:18

as per my knowledg., a table can have only one primary key. are you trying to create a composite primary key?

the below given code is used to create a composite key

String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
+ KEY_NAME + " TEXT," + KEY_ART + " TEXT,"
+ KEY_TAG + " TEXT  ," + KEY_MONAT + " TEXT ," + KEY_JAHR + " TEXT ,"+
"PRIMARY KEY("+KEY_TAG+","+KEY_MONAT+","+KEY_JAHR +"))";

or you can set any two of KEY_TAG,KEY_MONAT,KEY_JAHR as UNIQUE NOT NULL and remaining one as PRIMARY KEY. The difference between UNIQUE and PRIMARY KEY is that UNIQUE can be null and PRIMARY KEY cannot be null. and a table can have any numberof column with unique constraint butcan have only one primary key...

score 0 · Answer 2 · answered Jan 06 '15 at 13:05

To my knowledge, if you want to create a unique index, you have to do it in a separate query because you can only have one primary key. It doesn't have to include your primary key and it will cause any insert to be checked for those combinations. If a record exists, the old one will be dropped and a new one will be inserted in its place.

private static final String CREATE_TABLE_UNIQUE_INDEX =
"CREATE UNIQUE INDEX table_unique ON table_name (field1, field2, field3);";

score -1 · Answer 3 · answered Oct 24 '14 at 18:21

-1

Change your sql query to this, you missed some comma and spaces:

String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
        + KEY_NAME + " TEXT," + KEY_ART + " TEXT,"
        + KEY_TAG + " TEXT primary, " + KEY_MONAT + " TEXT primary, " + KEY_JAHR + " TEXT primary"+ ")";

The actual query should be this:

CREATE TABLE Events(name TEXT, art TEXT, tag TEXT primary, monat TEXT primary, jahr TEXT primary)

answered Oct 24 '14 at 18:21

chinglun

637
5
18

could you post the Logcat you're having this time? – chinglun Oct 24 '14 at 18:25
Try defining the primary keys with PRIMARY KEY (tag, monat, jahr) – chinglun Oct 24 '14 at 18:29
just the same. Caused by: android.database.sqlite.SQLiteException: table "Events" has more than one primary key (code 1): , while compiling: CREATE TABLE Events(name TEXT, art TEXT, tag TEXT PRIMARY KEY, monat TEXT PRIMARY KEY, jahr TEXT PRIMARY KEY) – M P Oct 24 '14 at 18:32
This post might help [Can I have multiple primary keys in a single table?](http://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table) – chinglun Oct 24 '14 at 18:36

Android - SQLite - syntax error (code 1): , while compiling: CREATE TABLE

3 Answers3

Linked