When creating an SQLite table from within Gambas, I'd like to mark one of the columns UNIQUE (such as column name "name" in the example code below). I'm not having any success. Is there a way to do that? I know I could check what's being entered against existing rows to make sure the new data is unique.

Hi  - I'd try creating the name field as an additional index.
You can read about it here on the SQLite site.

As a side note, you can create tables by storing the creation script as a file, reading it in and executing it as a SQL command.

This is an example that I've used to create a 'Category' table.

I find this better than using the code creation method as you can edit an existing file and tweak for new fields and name and it takes far less time to create large table sets.

If you have an existing database with a lot of tables, you can create a backup of all your tables with this subroutine:



…and create them all with this one.

Thanks Quin! I had created db in code (or with DB Browser for SQLite) for so long I had forgotten I can create a db using a script file in Gambas. After digging a little more I realized that I can create a unique index with code. Not exactly what I wanted, but just as good. After giving my use case more thought I decided I really don't even need a unique field since my db will contain just a few (maybe 6-8) records and it won't matter if any records are duplicates.

I'll file your information away somewhere for later use (or forgetting!   ).

 No problems.
I do a lot of work with SQLite but nothing so complex or large that would require multiple indexes to speed up search functions.
Instead, I found that a bit of effort on good validation in the input form is a better solution and you can give you users a better constructed error message to fix the issue.