To create a table using Sybase Central, see “Managing tables” in Introduction to Sybase IQ.
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 sample database with user
ID DBA and password sql.
For information on connecting to the sample database from DBISQL, see “Connecting to the sample database from Sybase Central or DBISQL.”
You can create tables with other tools in addition to DBISQL. The SQL statements described here are independent of the tool you are using.
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).
Each column has a data type. The skill_id is an integer (like 101), the skill_name is a fixed-width CHARACTER string containing up to 20 characters, and so on.
The phrase NOT NULL after their data types indicates that all columns in this example must contain a value.
In general, you would not create a table that has no primary key. To create a primary key, see “Creating primary and foreign keys”.
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.
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.
