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