Pramod,
You do need autoincrement - but it you also need to mark the column as
the primary key. Sorry about the confusion:
CREATE TABLE room (
_id INTEGER PRIMARY KEY AUTOINCREMENT
room_name TEXT )
CREATE TABLE equipment_in_room (
_id INTEGER PRIMARY KEY AUTOINCREMENT
switch_name TEXT
room_id INTEGER REFERENCES room (_id) )
Each row of equipment_in_room still has its own primary key (_id), to
identify the row in a unique way. It also has a reference to the room
that the equipment is in. There may be multiple equipment_in_room rows
for each room row.
For inserting, don't use execSql - as it doesn't return the primary key
value assigned to the new row.
Use SQLiteDatabase.insert
http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insert(java.lang.String,
java.lang.String, android.content.ContentValues)
or DatabaseUtils.InsertHelper:
http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html
This way you can get the assigned primary key value (in room) and
specify it when inserting into equipment_in_room.
ContentValues values_room = new ContentValues();
values_room.put ("room_name", "My room");
long room_id = db.insert ("room", "room_name", values_room);
Now you have the value of room_id assigned by SQLite to the new row in
table "room".
ContentValues values_equipment = new ContentValues();
values_equipment.put ("switch_name", "Cisco Catalyst");
*values_equipment.put("room_id", room_id);*
int equipment_id = db.insert ("equipment_in_room", "switch_name",
values_equipment);
The highlighted line above is what links the values in the two tables.
-- Kostya
13.11.2010 12:52, pramod.deore ?????:
You don't need 'autoincrement', I think it's for MS SQL Server.
Just don't specify a value for the primary key when inserting, and a new
unique value will be generated for you. It is then returned by insert() to
your code, so you can use it in the 'many' table.
Actually I didn't get you. After changing code as I understand what
you are saying
sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
SWITCH_TABLE_NAME +
" (SwitchID integer ,SwitchName
VARCHAR,FOREIGN KEY (SwitchID)REFERENCES roomtable (RoomID));");
sampleDB.execSQL("INSERT INTO " +
SWITCH_TABLE_NAME +
" Values ('"+s1+"');");
But now it gives me error as
11-13 15:08:21.024: ERROR/Database(773): Failure 1 (table switchtable
has 2 columns but 1 values were supplied) on 0x2f95d0 when preparing
'INSERT INTO switchtable Values ('Tube Light');'.
--
Kostya Vasilyev -- WiFi Manager + pretty widget -- http://kmansoft.wordpress.com
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en