Android Question SQLITE - is typeof column unique?

Guenter Becker

Active Member
Licensed User
Hello and a warm welcome to all of You.

I looked throught the internet and the forum but I did not find a clear and working solution for an easy (?) question.
I knew something about the sql PRAGMA Function but it does not deliver an information about the the columns type unique.
On some pages I read the information to use the PRAGMA index Function but what is if the respected column has no index? Or is an index auto genereated if a column is declared as unique?

My question: "What is to do to retrive the information from the Schema wether a columns type is unique or not". A small snipped of a B4A code solution would be welcome.

Thank you in advance and stay well
Guenter
 

Mahares

Expert
Licensed User
Longtime User
What is to do to retrive the information from the Schema wether a columns type is unique or not
Hi Mr. Becker:
You can see if any column of any table is UNIQUE or not, using this code with regard to the sqlite_master table. The 'sql' field shows you that for each column of each table in the database. I also commented some lines that show you more information. You can uncomment them to see if you want.
B4X:
Dim rs As ResultSet
    rs = Starter.SQL1.ExecQuery("SELECT * FROM sqlite_master")
    Do While rs.NextRow        
        Dim str As String = $"
        ${rs.getstring("sql")}"$
        LogColor(str, Colors.Magenta)
        'example of what the log displays: CREATE TABLE tblVehicles (Make int unique not null, Model INTEGER, P_DATE TEXT )

'        Dim str As String = $"${rs.getstring("tbl_name")}
'        ${rs.getstring("name")} 
'        ${rs.getstring("type")}
'        ${rs.getstring("sql")}"$
'        LogColor(str, Colors.Magenta)
    Loop
    rs.close
You stay well too. I heard Munich beat Paris for the European football cup
 
Upvote 0

Claudio Oliveira

Active Member
Licensed User
Longtime User
Hi, Mr. Becker!

Try this

B4X:
Sub check_unique( strTable As String, strColumn As String) as Boolean
    Dim ddl As String
    Dim isUnique As Boolean = False
     
    'Get table DDL
    ddl = Main.SQ.ExecQuerySingleResult($"SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = '${strTable}'"$)
 
    'Clean up brackets, opening parenthesis and replacing closing parenthesis with a comma
    ddl = ddl.SubString(ddl.IndexOf("(")+1).Replace(")",",").Replace("[","").Replace("]","")

    'extracts the desired field definiion from DDL and check if it contains the UNIQUE clause
    ddl = ddl.SubString(ddl.IndexOf(strColumn))
    ddl = ddl.SubString2(0, ddl.IndexOf(","))
    If ddl.Contains("UNIQUE") Then
        isUnique=True
    End If

    Return isUnique
 
End Sub
Please note that this works only when the UNIQUE clause is part of the column definition.
If the UNIQUE CONSTRAINT is specified in a separate statement in DDL, this code will not work.

On a table declared like this:

B4X:
CREATE TABLE [TEST](
  [TEST1] INTEGER CONSTRAINT [UC_TEST1] UNIQUE,
  [TEST2] TEXT,
  [TEST3] DATE CONSTRAINT [UC_TEST3] UNIQUE,
  CONSTRAINT [UC_TEST2] UNIQUE([TEST2]) ON CONFLICT FAIL);

This code should work for columns TEST1 and TEST3, but NOT for column TEST2, even though it's a unique constraint column.
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Your last few lines should be:
B4X:
ddl = ddl.SubString(ddl.IndexOf(strColumn))
    ddl = ddl.SubString2(0, ddl.IndexOf(","))
    ddl=ddl.ToUpperCase   '<----  Add this
    If ddl.Contains("UNIQUE") Then
        isUnique=True
    End If
    Return isUnique
in case the schema was written using lower case. Otherwise, it can overlook it.
 
Upvote 0

Claudio Oliveira

Active Member
Licensed User
Longtime User
Your last few lines should be:
B4X:
ddl = ddl.SubString(ddl.IndexOf(strColumn))
    ddl = ddl.SubString2(0, ddl.IndexOf(","))
    ddl=ddl.ToUpperCase   '<----  Add this
    If ddl.Contains("UNIQUE") Then
        isUnique=True
    End If
    Return isUnique
in case the schema was written using lower case. Otherwise, it can overlook it.
I Agree
 
Upvote 0

Guenter Becker

Active Member
Licensed User
Dear colleques,
thank you for the quick answer I will test it in my App. And yes the bavarians won the cup. They are a very good team at present but i think lets have a look wether their excellent playing may influence the quality of the German national soccer team. To say it with Mr. Bond "The hope dies at least!".
Best regards
Guenter
 
Upvote 0

Guenter Becker

Active Member
Licensed User
Thank you solution works fine.
 
Upvote 0
Top