Is there a way to define primary keys using SQLCreateTable?


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.

Ariel Sepulveda

Posted 2015-03-24T23:25:17.893

Reputation: 1 617



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.

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

The help example has a filename, whereas SQLite documentation says no filename.

Seems to work for me with no filemane.


Posted 2015-03-24T23:25:17.893

Reputation: 896