Creating tables

Creating tables in Sybase Central

To create a table using Sybase Central, see “Managing tables” in Introduction to Sybase IQ.

SQL statement for creating tables

The SQL statement for creating tables is CREATE TABLE.

This section describes how to use the CREATE TABLE statement. The examples in this section use the sample database. To try the examples, run dbisql and connect to the demo database with user ID DBA and password sql.

For information on connecting to the demo database from dbisql, see “Connecting to the demo database from Sybase Central or Interactive SQL.”

You can create tables with other tools in addition to Interactive SQL. The SQL statements described here are independent of the tool you are using.

Creating tables with SQL

The following statement creates a new, permanent IQ table to describe qualifications of employees within a company. The table has columns to hold an identifying number, a name, and a type (say technical or administrative) for each skill.

CREATE TABLE skill (
skill_id INTEGER NOT NULL,
skill_name CHAR( 20 ) NOT NULL,
skill_type CHAR( 20 ) NOT NULL
)

You can execute this command by typing it into the dbisql command window, and pressing the execute key (F9).

By internally executing the COMMIT statement before creating the table, Sybase IQ makes permanent all previous changes to the database. There is also a COMMIT after the table is created.

For a full description of the CREATE TABLE statement, see CREATE TABLE statement in Reference: Statements and Options. For information about building constraints into table definitions using CREATE TABLE, see Chapter 9, “Ensuring Data Integrity”.

WARNING! Altering or creating global or base tables can interfere with other users of the database. For large tables, ALTER or CREATE TABLE can be a time-consuming operation. CREATE TABLE processing delays execution of other IQ processes until the statement completes. Although you can execute ALTER TABLE statements while other connections are active, you cannot execute them while any other connection uses the table to be altered. ALTER TABLE processing excludes other requests referencing the table being offered while the statement processes.

Specifying data types

When you create a table, you specify the type of data that each column holds.

You can also define customized data types for your database. See Chapter 3, “SQL Data Types,” in Reference: Building Blocks, Tables, and Procedures for a list of supported data types, or see CREATE DOMAIN statement in Reference: Statements and Options for details on how to create a customized data type.