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.

Creating a table in a database.

Here is an example that creates a table.

This loads DatabaseLink and connects to the demo database.

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.

SQLTableNames verifies that the table exists in the database.

SQLColumnNames verifies the columns in the table.

SQLCreateTable accepts two options.

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.

This drops the tables and closes the connection.

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.