Android Question SQLITE - is typeof column unique?

[Guenter Becker]

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

 

[Erel]

Moved to the questions forum.

 

[Mahares]

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.

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

 

[Claudio Oliveira]

Hi, Mr. Becker!

Try this

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:

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.

 

[Mahares]

Your last few lines should be:

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.

 

[Claudio Oliveira]

Your last few lines should be:

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

 

[Guenter Becker]

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

 

[Guenter Becker]

Thank you solution works fine.