Creating Tables

SQLCreateTable creates a new table in a database. An alternative, using raw SQL, is described in "Creating Tables with Raw SQL".

If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".

When creating a table, the result of SQLCreateTable is an integer specifying the number of rows affected by the query. If the table is created correctly, this integer will always be zero, as no rows are affected when creating a new table.

SQLCreateTable[conn,table,{columns},opts]create an SQL table

Creating a table in a database.

Here is an example that creates a table.

This loads DatabaseLink and connects to the demo database.

Click for copyable input

SQLCreateTable creates a table. The columns are given as a list of SQLColumn expressions. In the following example, a new table, DATATYPESTABLE, is created that has one column for each of the data types returned from SQLDataTypeNames. The column, TINYINTCOL, is configured so that it cannot be set to Null. However, each binary column can be set to Null. The database default for "Nullable" is used for every other column that does not specify the "Nullable" option. The character-based columns are limited to a specific data length; other columns use the default data length for their type.

Click for copyable input
Click for copyable input

SQLTableNames verifies that the table exists in the database.

Click for copyable input

SQLColumnNames verifies the columns in the table.

Click for copyable input

SQLCreateTable accepts two options.

option name
default value
"Timeout"Automaticset the timeout for a query
"Index"Nonecolumn(s) to index

Options of SQLCreateTable.

"Timeout" can be used to cancel a query if it takes too long to execute. "Index" can be used to add an index to one or more columns.

Click for copyable input

This drops the tables and closes the connection.

Click for copyable input

Certain databases support further options for columns, such as whether a column is a key or whether it auto-increments. If these options are desired, then a raw SQL statement should be used to create the table. "Creating Tables with Raw SQL" has some ideas and examples.