Guidelines for Creating Tables

Create tables using Sybase Central or Interactive SQL.

Table Creation with Sybase Central

To create a table using Sybase Central, see the online help.

Table Creation with Interactive SQL

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.

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.

Example

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.

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 Reference: Statements and Options.

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 Reference: Building Blocks, Tables, and Procedures for a list of supported data types. See the CREATE DOMAIN statement in Reference: Statements and Options for details on how to create a customized data type.

Related concepts
How Locking Works
IQ PAGE SIZE Parameter Guidelines
Number of Distinct Values
Rules and Checks for Valid Data