Is there a way to define primary keys using SQLCreateTable?

Question

I would like to create a db table and specify the primary key columns using the command SQLCreateTable. I know I could use SQLExecute but in this case I'm only interested in the right parameters for SQLCreateTable.

Answer 1

Straight from the Help documents "DatabaseLink/ref/SQLCreateTable" SQLCreateTable is a SQLTable with list of SQLColumn(s). Now again from the Help document "DatabaseLink/ref/SQLColumn" the "PrimaryKey" is an attribute (option) of SQLColumn. There is a primary key example in the help docs using JDBC SQLite. Whether it works or not is another issue.

Needs["DatabaseLink`"]
conn = OpenSQLConnection[JDBC["SQLite(Memory)", ""]];
SQLCreateTable[conn, 
  SQLTable["TEST"], {SQLColumn["X", "DataTypeName" -> "INTEGER", 
    "PrimaryKey" -> True], 
   SQLColumn["Y", "DataTypeName" -> "CHAR", "DataLength" -> 3]}];
SQLTablePrimaryKeys[conn, "TEST", "ShowColumnHeadings" -> True]
SQLDropTable[conn, "TEST"];
CloseSQLConnection[conn];

The help example has a filename, whereas SQLite documentation says no filename. https://www.sqlite.org/inmemorydb.html

Seems to work for me with no filemane.