Creating Tables with Raw SQL

The raw SQL command CREATE TABLE creates tables in a database. An alternative is to use the Wolfram Language command SQLCreateTable, described in "Creating Tables".

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 SQLExecute 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.

Here is an example that creates a table. This loads DatabaseLink and connects to the demo database.

When a table is created, options can be given to restrict how data is stored within the database. In the following, a table is created with four columns. The USERNAME is a string-based column that cannot be Null and is the primary key. (A primary key is important to a table as it uniquely identifies a row within the table.) The other three columns (ADDRESS, CITY, and ZIPCODE) are regular string-based columns. However, they must be unique among all rows.

In this example, a table with three columns is created. The first column is an integer that is an identity. This means that it is the primary key for the table and its value will be automatically incremented in each row. In other words, the value is not required when data is inserted; instead, the value will be the next available increment. The USERNAME is a string-based column that is the foreign key to the ADDRESSES table. The third column is a bit that has a default of 1 (i.e. if a value is not supplied when data is inserted, it will be set to 1).

SQLTableNames verifies the tables exist in the database.

SQLColumnNames verifies the columns were created in the database.

This deletes the tables and closes the connection.

Other options may be available to you when creating tables, depending on the database being used. See your database documentation for information on what options are specifically available.