When creating tables for compatibility, be aware of the following items.
For compatible treatment of NULL:
Adaptive Server Anywhere and IQ assume that columns can be null unless NOT NULL is stated in the column definition. You can change this behavior by setting the database option ALLOW_NULLS_BY_DEFAULT to the Transact-SQL compatible setting of OFF.
Anywhere assumes that BIT columns only cannot be NULL.
Adaptive Server Enterprise assumes that columns cannot be null unless NULL is stated.
Sybase IQ enforces check constraints on base, global temporary, and local temporary tables, and user-defined data types. Users can log check integrity constraint violations and specify the number of violations that can occur before a LOAD statement rolls back.
Sybase IQ does not allow the creation of a check constraint that it cannot evaluate, such as check constraints comprised of user-defined functions, proxy tables, or non-IQ tables. Constraints that cannot be evaluated are detected the first time the table on which the check constraint is defined is used in a LOAD, INSERT, or UPDATE statement. Sybase IQ does not allow check constraints containing:
Subqueries
Expressions specifying a host language parameter, an SQL parameter, or a column as the target for a data value
Set functions
Invocations of non-deterministic functions or functions that modify data
If you have databases created with a previous version of Sybase IQ, you should run the stored procedure sp_iqprintconstraints, to list all IQ tables and columns in a format that allows you to recreate them after deletion. If you want to drop all constraints on IQ tables in the database, you can then run the sp_iqdropconstraints procedure.
Adaptive Server Enterprise and Adaptive Server Anywhere enforce CHECK constraints. Adaptive Server Anywhere allows subqueries in check constraints.
Sybase IQ supports user-defined data types which allow constraints to be encapsulated in the data type definition.
Sybase IQ enforces referential integrity as described in Chapter 9, “Ensuring Data Integrity” in the Sybase IQ System Administration Guide.
Actions for enforcing integrity are supported as follows:
Adaptive Server Anywhere supports all ANSI actions: SET NULL, CASCADE, DEFAULT, RESTRICT
Adaptive Server Enterprise supports two of these actions: SET NULL, DEFAULT.
You can achieve CASCADE in Adaptive Server Enterprise
by using triggers instead of referential integrity.
IQ supports the RESTRICT action only.
IQ does not support NOT NULL FOREIGN KEY.
IQ has the restriction that a column cannot be both a candidate key and a foreign key at the same time.
Default value support differs as follows:
Adaptive Server Enterprise and Adaptive Server Anywhere support specifying default values for a column.
Only Adaptive Server Anywhere supports DEFAULT UTC TIMESTAMP.
IQ does not support specifying default values.
Identity column support differs as follows:
Sybase IQ supports IDENTITY or DEFAULT AUTOINCREMENT as a default value. Sybase IQ supports identity columns of any numeric type with any precision and scale 0, and the column may be NULL. Sybase IQ identity columns must be positive and are limited by the range of the data type. Sybase IQ supports a single identity column per table, and requires database option IDENTITY_INSERT set to ON for explicit inserts, drops, and updates. The table may contain data when adding an identity column. Tables derived using SELECT INTO do not have Identity/Autoincrement columns. Sybase IQ views cannot contain IDENTITY/DEFAULT AUTOINCREMENT columns.
Adaptive Server Anywhere supports AUTOINCREMENT default value. Adaptive Server Anywhere supports identity columns of any numeric type with any allowable scale and precision. The identity column value may be positive, negative or zero, limited by the range of the data type. Adaptive Server Anywhere supports any number of identity columns per table, and does not require identity_insert for explicit inserts, drops, and updates. The table must be empty when adding identity column(s). ASA identity columns can be altered to be non-identity columns and vice versa. You can add or drop AUTOINCREMENT columns from ASA views.
Adaptive Server Enterprise supports a single identity column per table. ASE identity columns are restricted to only numeric data type scale 0, maximum precision 38. They must be positive, are limited by the range of the data type, and cannot be null. Adaptive Server Enterprise requires identity_insert for explicit inserts and drops, but not for updates to the identity column. The table may contain data when adding an identity column. ASE users cannot explicitly set the next value chosen for an identity column. ASE views cannot contain IDENTITY/AUTOINCREMENT columns. When using SELECT INTO under certain conditions, ASE allows Identity/Autoincrement columns in the result table if they were in the table being selected from.
Computed column support differs as follows:
Adaptive Server Anywhere supports computed columns that can be indexed
Adaptive Server Enterprise and IQ do not.
You can create a temporary table by placing a pound sign (#) without an owner specification in front of the table name in a CREATE TABLE statement. These temporary tables are Sybase IQ declared temporary tables, and are available only in the current connection. For information about declared temporary tables in Sybase IQ, see the DECLARE LOCAL TEMPORARY TABLE statement.
For information about creating tables, see the CREATE TABLE statement.
Physical placement of a table is carried out differently in Adaptive Server Enterprise and in Sybase IQ. Sybase IQ supports the ON segment-name clause, but segment-name refers to a Sybase IQ dbspace.
