Android - relational database SQLite with multiple tables

Question

I'm trying to build a database using 21 tables each bind with constraints. I have implemented the database in PHPmyAdmin and then download the structure script to import in my Android SQLite database.

But I would like to know what are the good practices in building relational databases with many tables in Android, and how to create them.

currently I have a class implementing SQLiteOpenHelper:

public class SqlSig extends SQLiteOpenHelper {

@Override
public void onCreate(SQLiteDatabase db) {
    for(int i = 0; i != ConfigBDD.requetes.length; i++){
        db.execSQL(ConfigBDD.requetes[i]);
    }
}
}

ConfigBDD.requetes[i] fetch a string array containing tables creation requests and constraints requests. This code doesn't work at all, and logCat show an error with the PRIMARY KEY instruction:

 05-29 15:13:51.992: E/Database(8187): Failure 1 (near "KEY": syntax error) on 0x15c4b8
 when preparing 'CREATE TABLE IF NOT EXISTS `arret` ( `id` int(11) NOT NULL , `id_externe`
 varchar(10) DEFAULT NULL, `id_pid` int(11) NOT NULL, `nom` varchar(50) NOT NULL,
 `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_pid` (`id_pid`) )
 DEFAULT CHARSET=utf8 =1 ;'.

I am pretty sure there are other ways to create this database but did not found any explanations about multi tables Android SQLite database creation...

The error is quite clear - your SQL syntax is incorrect. What's that KEY entry following the PRIMARY KEY? Try removing that. Should that be a FOREIGN KEY? — duffymo
– duffymo, Commented May 29, 2012 at 13:22
How can it be incorrect, it's automatically generated by phpmyadmin and it works perfectly to re-create the base in a web context. My question is also about good practices to create the database, more than just a weird syntax error — YannPl
– YannPl, Commented May 29, 2012 at 13:25
PHPmyAdmin.. isn't that for administrating MySQL - it's not super likely that your tables are going to import cleanly into SQLite if that is the case. — Jens
– Jens, Commented May 29, 2012 at 13:26
How can it be incorrect? I don't know, but that's what the error mesage is telling you. Are still insisting that you're correct, in spite of an error message when you create the database? — duffymo
– duffymo, Commented May 29, 2012 at 13:35
@Zarkaos, MySQL is not SQLite... you must use the proper syntax for the database engine you are using. What you've basically said is that my Lamborghini key should be able to start my Ford Taurus. Just ain't so. — Barak
– Barak, Commented May 29, 2012 at 15:30

Jens · Accepted Answer · 2012-05-29 13:32:24Z

5

Your:

CREATE TABLE IF NOT EXISTS `arret` ( 
  `id` int(11) NOT NULL , 
  `id_externe` varchar(10) DEFAULT NULL, 
  `id_pid` int(11) NOT NULL, 
  `nom` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL, 
   PRIMARY KEY (`id`), 
   KEY `id_pid` (`id_pid`))
DEFAULT CHARSET=utf8 =1 ;

should probably look something like this if you want it to work in SQLite.

CREATE TABLE IF NOT EXISTS arret ( 
  id INTEGER PRIMARY KEY NOT NULL , 
  id_externe TEXT DEFAULT NULL,
  id_pid INTEGER NOT NULL,
  nom TEXT NOT NULL,
  description TEXT
);

followed by a CREATE INDEX for your id_pid which is what KEY implies in MySQL.

answered May 29, 2012 at 13:32

Jens

17.1k4 gold badges59 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Android - relational database SQLite with multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related