Database export schema options

On the database export Schema dialog box, you can set options for database export (for databases that support these options), set SQL data types for your fields, specify which fields are primary keys, and customize the CREATE TABLE statement generated upon export.

The dialog box has several parts:

  • The section at the top (if displayed) contains options for export to a database that supports these options. This section is not displayed if you are not connected to such a database.
  • The text field in the center displays the template used to generate the CREATE TABLE command, which by default follows the format:

    CREATE TABLE <table-name> <(table columns)>

  • The table in the lower portion enables you to specify the SQL data type for each field and to indicate which fields are primary keys as discussed below. The dialog box automatically generates the values of the <table-name> and <(table columns)> parameters based on the specifications in the table.

Setting database export options

If this section is displayed, you can specify a number of settings for export to the database. The database types that support this feature are as follows.

  • SQL Server Enterprise and Developer editions. See the topic Options for SQL Server for more information.
  • Oracle Enterprise or Personal editions. See the topic Options for Oracle for more information.

Customizing CREATE TABLE statements

Using the text field portion of this dialog box, you can add extra database-specific options to the CREATE TABLE statement.

  1. Select the Customize CREATE TABLE command check box to activate the text window.
  2. Add any database-specific options to the statement. Be sure to retain the text <table-name> and (<table-columns>) parameters because these are substituted for the real table name and column definitions by IBM® SPSS® Modeler.

Setting SQL data types

By default, IBM SPSS Modeler enables the database server to assign SQL data types automatically. To override the automatic type for a field, find the row corresponding to the field and select the desired type from the drop-down list in the Type column of the schema table. You can use Shift-click to select more than one row.

For types that take a length, precision, or scale argument (BINARY, VARBINARY, CHAR, VARCHAR, NUMERIC, and NUMBER), you should specify a length rather than allow the database server to assign an automatic length. For example, specifying a sensible value, such as VARCHAR(25), for length ensures that the storage type in IBM SPSS Modeler will be overwritten if that is your intention. To override the automatic assignment, select Specify from the Type drop-down list and replace the type definition with the desired SQL type definition statement.

The easiest way to do this is to first select the type that is closest to the desired type definition and then select Specify to edit that definition. For example, to set the SQL data type to VARCHAR(25), first set the type to VARCHAR(length) from the Type drop-down list, and then select Specify and replace the text length with the value 25.

Primary keys

If one or more columns in the exported table must have a unique value or combination of values for every row, you can indicate this by selecting the Primary Key check box for each field that applies. Most databases will not allow the table to be modified in a manner that invalidates a primary key constraint and will automatically create an index over the primary key to help enforce this restriction. (Optionally, you can create indexes for other fields in the Indexes dialog box. See the topic Database Export Index Options for more information. )