Android Question DBUtils - Foreign Key support & multiple fields in Primary Key

Jeff (Not 24) · Nov 9, 2013

Hi ... I am just starting to use B4A and really love it.

I would like to use DBUTILS to create my Database Tables. However, I have 2 requirements that don't seem to be in the current version of DBUTILS.

1. Foreign Key Support which is documented here:
http://www.sqlite.org/foreignkeys.html

I believe the format is:
[FieldName] TEXT NOT NULL CONSTRAINT [ConstraintName] REFERENCES [OtherTableName]([FieldInOtherTable]),

2. Primary Keys with multiple columns. These are SQLite as constraints and the format is:
PRIMARY KEY (field1, field2, field3, field4)
See http://www.sqlite.org/syntaxdiagrams.html#table-constraint

Any chance of these being added into DBUTILS? Or help in getting me to add it into my copy of DBUTILS? Or would it be better for me to use an external program to create the DB on the PC and then copy it into my application directory on the Android device.

Any help will be appreciated.

Thanks ... Jeff

Erel · Nov 10, 2013

DBUtils is a set of helper methods. You can build the table directly with SQL.ExecuteNonQuery and set the foreign key or the multi-column primary key. It will be simpler than adding a general CreateTable method that tries to handle all cases.

tdocs2 · May 16, 2014

Erel said:
DBUtils is a set of helper methods. You can build the table directly with SQL.ExecuteNonQuery and set the foreign key or the multi-column primary key. It will be simpler than adding a general CreateTable method that tries to handle all cases.

Greetings, all.
I am pretty new at SQL and dependent on DBUtils which is GREAT, so this is an extension to the question of having multiple columns as the primary key. I modified one of Klaus SQL examples. Is this code correct? Will the primary key be first name and then last name? How would I code it if Primary key consisted of ID and City?

B4X:

Sub CreateDataBase
    Dim Query As String
     Query = "CREATE TABLE persons (ID INTEGER, PRIMARY KEY(FirstName TEXT, LastName TEXT), City TEXT)"
    SQL1.ExecNonQuery(Query)
End Sub

Thank you in advance. Any help is welcomed.

LucaMs · May 16, 2014

It is probably better to use an external tool.

These are correct:

B4X:

CREATE TABLE persons (ID INTEGER, FirstName TEXT, LastName TEXT, City TEXT, PRIMARY KEY(FirstName, LastName))


CREATE TABLE persons (ID INTEGER, FirstName TEXT, LastName TEXT, City TEXT, PRIMARY KEY(ID, City))

tdocs2 · May 16, 2014

LucaMs said:
It is probably better to use an external tool.

These are correct:

B4X:

CREATE TABLE persons (ID INTEGER, FirstName TEXT, LastName TEXT, City TEXT, PRIMARY KEY(FirstName, LastName)) CREATE TABLE persons (ID INTEGER, FirstName TEXT, LastName TEXT, City TEXT, PRIMARY KEY(ID, City))

Grazie, Luca.

Best regards.

Sandy

peggjones · Jul 16, 2014

Has anyone got referential integrity to work?

I have set up a small database as described here http://www.sqlite.org/foreignkeys.html.

But I am still allowed to delete a record on the main file which has children on the second file.

I have set up the foreign key on the second file and done the SQL.ExecNonQuery("PRAGMA foreign_keys = ON") bit.

Does anyone know if it is possible that I don't have the right version of SQL?

LucaMs · Jul 16, 2014

The last time I read about this topic, I have found that it is only an appearance, no substance.

But you do not listen to me, search on the web

keirS · Jul 17, 2014

peggjones said:
Has anyone got referential integrity to work?

I have set up a small database as described here http://www.sqlite.org/foreignkeys.html.

But I am still allowed to delete a record on the main file which has children on the second file.

I have set up the foreign key on the second file and done the SQL.ExecNonQuery("PRAGMA foreign_keys = ON") bit.

Does anyone know if it is possible that I don't have the right version of SQL?

Yes I have got it working. Only use ON CASCADE DELETE at the moment though. One thing is I didn't use ExecNonQuery("PRAGMA foreign_keys = ON") . I use the reflection library and call setForeignKeyConstraintsEnabled.

B4X:

 Dim R As Reflector
  R.Target = oSQL
  R.Target = R.GetField("db")
    R.Runmethod2("setForeignKeyConstraintsEnabled",True,"java.lang.boolean")

peggjones · Jul 18, 2014

Thanks for that. I've tried it but still no joy. I take it that oSQL is an instance if the SQL class.

keirS · Jul 18, 2014

Try this (DB file attached). Order details has a foreign key of odernumber and is set to cascade delete.

B4X:

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim oSQL As SQL
   
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

End Sub

Sub Activity_Create(FirstTime As Boolean)
    Dim oCursor As Cursor
    Dim cntr As Int
    Dim R As Reflector
   
    File.Copy(File.DirAssets,"fkeytest.db",File.DirDefaultExternal,"fkeytest.db")
   
    oSQL.Initialize(File.DirDefaultExternal,"fkeytest.db",True)
    Dim R As Reflector
    R.Target = oSQL
    R.Target = R.GetField("db")
    R.Runmethod2("setForeignKeyConstraintsEnabled",True,"java.lang.boolean")
   
    oCursor = oSQL.ExecQuery("PRAGMA foreign_keys")
    oCursor.Position = 0
    Log("foreign_keys = " & oCursor.GetString2(0))
   
    oCursor = oSQL.ExecQuery("Select * from orderdetails where ordernumber = 10103")
    Log("No Rows For Orderdetails where ordernumber = 10103: " & oCursor.RowCount)
    oCursor = oSQL.ExecQuery("Select * from orders where ordernumber = 10103")
    Log("No. Rows For Orders where ordernumber = 10103: " & oCursor.RowCount)
    Log("Deleteteing OrderNumber 10103")
    oSQL.ExecNonQuery("DELETE FROM ORDERS WHERE ordernumber = 10103")
    oCursor = oSQL.ExecQuery("Select * from orderdetails where ordernumber = 10103")
    Log("No. Rows For Orderdetails where ordernumber = 10103: " & oCursor.RowCount)
    oCursor = oSQL.ExecQuery("Select * from orders where ordernumber = 10103")
    Log("No. Rows For Orders where ordernumber = 10103: " & oCursor.RowCount)
   
   
   
End Sub

Log output should show:

B4X:

foreign_keys = 1
No Rows For Orderdetails where ordernumber = 10103: 16
No. Rows For Orders where ordernumber = 10103: 1
Deleteteing OrderNumber 10103
No. Rows For Orderdetails where ordernumber = 10103: 0
No. Rows For Orders where ordernumber = 10103: 0

peggjones · Jul 19, 2014

Thanks so much for all the trouble you've taken to help me.

I tried your example and it worked perfectly. So there is nothing wrong with the version of SQL I am using.

However when I import your code into my program it still doesn't work. I can still delete a parent which has a child.

Can't ask you to spend any more time on it. I'll find a work around.

Many thanks.

peggjones · Jul 20, 2014

KeirS could I trouble you just one more time to cast your eye over this?

SQL.ExecNonQuery("CREATE TABLE Bosses (bossno INTEGER PRIMARY KEY , desc TEXT, sdate TEXT, ssos int, ddate TEXT, dsos int, actioned TEXT,notes TEXT,adate TEXT, atime TEXT, asos int ,cdate TEXT, ctime TEXT, csos int,device text,ccmail text,importance text)")

SQL.ExecNonQuery("CREATE TABLE Bosssubs (bosssubno INTEGER PRIMARY KEY , bossno int, desc TEXT, bdate TEXT, btime TEXT, foreign key(bossno) REFERENCES Bosses(bossno) on delete RESTRICT)")

This is how I Have created my database in a B4A program. I am able to for example to delete a record on Bosses where bossno = 123 even if there is a record on Bosssubs where bossno = 123.

After running this code that you gave me foreign key = 1 is logged.

Dim R As Reflector
R.Target = SQL
R.Target = R.GetField("db")
R.Runmethod2("setForeignKeyConstraintsEnabled",True,"java.lang.boolean")

oCursor = oSQL.ExecQuery("PRAGMA foreign_keys")
oCursor.Position = 0
Log("foreign_keys = " & oCursor.GetString2(0))

Can you perhaps think of someting else that should be set?

Many thanks.

LucaMs · Jul 20, 2014

SQLite Foreign Key Support.

I have not run tests, but I do not think it is necessary to use reflector or javaobject for this.

peggjones · Jul 20, 2014

After hours messing around with this I have discovered that for me ON DELETE CASCADE works but ON DELETE RESTRICT does not!

I can't believe this really happens, surely I must have misunderstood or done something wrong!

Does anyone know what should happen if I try to delete a parent record which has a child?

Thanks to all.

LucaMs · Jul 20, 2014

Can I afford a suggestion?

You should choose better names, both fields and tables names.

Moreover, given that DESC is a keyword, I substituted it with descr.

Try the attachment.

(it should be: LM THE BOSS

)

keirS · Jul 20, 2014

peggjones said:
KeirS could I trouble you just one more time to cast your eye over this?

SQL.ExecNonQuery("CREATE TABLE Bosses (bossno INTEGER PRIMARY KEY , desc TEXT, sdate TEXT, ssos int, ddate TEXT, dsos int, actioned TEXT,notes TEXT,adate TEXT, atime TEXT, asos int ,cdate TEXT, ctime TEXT, csos int,device text,ccmail text,importance text)")

SQL.ExecNonQuery("CREATE TABLE Bosssubs (bosssubno INTEGER PRIMARY KEY , bossno int, desc TEXT, bdate TEXT, btime TEXT, foreign key(bossno) REFERENCES Bosses(bossno) on delete RESTRICT)")

This is how I Have created my database in a B4A program. I am able to for example to delete a record on Bosses where bossno = 123 even if there is a record on Bosssubs where bossno = 123.

After running this code that you gave me foreign key = 1 is logged.

Dim R As Reflector
R.Target = SQL
R.Target = R.GetField("db")
R.Runmethod2("setForeignKeyConstraintsEnabled",True,"java.lang.boolean")

oCursor = oSQL.ExecQuery("PRAGMA foreign_keys")
oCursor.Position = 0
Log("foreign_keys = " & oCursor.GetString2(0))

Can you perhaps think of someting else that should be set?

Many thanks.

Two differences as far as I can see. I am using a column constraint and not a table constraint (AFAIK this shouldn't matter in this situation). So the Create Table command would look like this.

B4X:

CREATE TABLE Bosssubs (bosssubno INTEGER PRIMARY KEY , bossno int  REFERENCES Bosses(bossno) on delete RESTRICT, desc TEXT, bdate TEXT, btime TEXT)

Secondly I am explicitly creating indexes for my Primary Key and Foreign Key.

B4X:

CREATE UNIQUE INDEX bosses_bossno ON bosses ( bossno COLLATE BINARY ASC )
CREATE  INDEX bosssubs_bossno ON bosssubs ( bossno COLLATE BINARY ASC )

I have modified my DB (attached) and code to do DELETE RESTRICT.

B4X:

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim oSQL As SQL
  
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

End Sub

Sub Activity_Create(FirstTime As Boolean)
    Dim oCursor As Cursor
    Dim cntr As Int
    Dim R As Reflector
  
     File.Copy(File.DirAssets,"fkeytest.db",File.DirDefaultExternal,"fkeytest.db")
    
    oSQL.Initialize(File.DirDefaultExternal,"fkeytest.db",True)
    Dim R As Reflector
     R.Target = oSQL
     R.Target = R.GetField("db")
     R.Runmethod2("setForeignKeyConstraintsEnabled",True,"java.lang.boolean")
    
    oCursor = oSQL.ExecQuery("PRAGMA foreign_keys")
    oCursor.Position = 0
    Log("foreign_keys = " & oCursor.GetString2(0))
    
    oCursor = oSQL.ExecQuery("Select * from orderdetails where ordernumber = 10103")
    Log("No Rows For Orderdetails where ordernumber = 10103: " & oCursor.RowCount)
    oCursor = oSQL.ExecQuery("Select * from orders where ordernumber = 10103")
    Log("No. Rows For Orders where ordernumber = 10103: " & oCursor.RowCount)
    Log("Deleteteing OrderNumber 10103")
    Try
        oSQL.ExecNonQuery("DELETE FROM ORDERS WHERE ordernumber = 10103")
        oCursor = oSQL.ExecQuery("Select * from orderdetails where ordernumber = 10103")
        Log("No. Rows For Orderdetails where ordernumber = 10103: " & oCursor.RowCount)
        oCursor = oSQL.ExecQuery("Select * from orders where ordernumber = 10103")
        Log("No. Rows For Orders where ordernumber = 10103: " & oCursor.RowCount)
    Catch
         If LastException.Message.Contains("foreign key constraint failed") Then
            Log("Cannot delete order 10103 it has children!")
         Else
            Log(LastException.Message)
         End If
    End Try
    
  
  
End Sub

Log File:

B4X:

foreign_keys = 1
No Rows For Orderdetails where ordernumber = 10103: 16
No. Rows For Orders where ordernumber = 10103: 1
Deleteteing OrderNumber 10103
Cannot delete order 10103 it has children!

peggjones · Jul 21, 2014

Thanks very much that definitely works!

peggjones · Jul 21, 2014

LucaMs said:
Can I afford a suggestion?

You should choose better names, both fields and tables names.

Moreover, given that DESC is a keyword, I substituted it with descr.

Try the attachment.

(it should be: LM THE BOSS )

Lee Gillie CCP · Jun 5, 2015

In DBUtils, I believe this would create a single field or a multiple field primary key.

B4X:

'Creates a new table with the given name.
'FieldsAndTypes - A map with the fields names as keys and the types as values.
'You can use the DB_... constants for the types.
'PrimaryKey - The column(S) that will be the primary key, separated by commas. Pass empty string if not needed.
' ---- could this replace CreateTable as a more powerful version?
Sub CreateTable2(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("(")
    For i = 0 To FieldsAndTypes.Size - 1
        Dim field, ftype As String
        field = FieldsAndTypes.GetKeyAt(i)
        ftype = FieldsAndTypes.GetValueAt(i)
        If i > 0 Then sb.Append(", ")
        sb.Append("[").Append(field).Append("] ").Append(ftype)
        ' If field = PrimaryKey Then sb.Append(" PRIMARY KEY")
    Next
    If PrimaryKey.Length>0 Then
         sb.Append(", PRIMARY KEY(").Append(PrimaryKey).Append(")" )
    End If
   
    sb.Append(")")
    Dim query As String
    query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] " & sb.ToString
    Log("CreateTable: " & query)
    SQL.ExecNonQuery(query)
End Sub

Android Question DBUtils - Foreign Key support & multiple fields in Primary Key

New Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Active Member

Expert

Well-Known Member

Active Member

Well-Known Member

Attachments

Active Member

Active Member

Expert

Active Member

Expert

Attachments

Well-Known Member

Attachments

Active Member

Active Member

Active Member