Creating Tables with Raw SQL
The raw SQL command CREATE TABLE creates tables in a database. An alternative is to use the Mathematica
, 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 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
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
, 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).
verifies the tables exist in the database.
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.