Home

	Expand Search

ASA User's Guide

Part 1 Working with Databases

Chapter 3: Working with Database Objects

Working with tables

Working with tables

When the database is initialized, the only tables in the database are the system tables, which hold the database schema.

This section describes how to create, alter, and delete tables from a database You can execute the examples in Interactive SQL, but the SQL statements are independent of the administration tool you use.

To make it easier for you to re-create the database schema when necessary, create command files to define the tables in your database . The command files should contain the CREATE TABLE and ALTER TABLE statements.

Creating tables

Creating tables in Sybase Central

Sybase Central provides a tool called the table editor. In the table editor, you can create a table definition by filling out a spreadsheet-like form.

Connect to the database.

Click the Tables folder for that database.

In the right panel, double-click Add Table.

Enter the features you want in the Table Editor.

Click OK to create the table.

SQL statement for creating tables

The SQL statement for creating tables is the CREATE TABLE statement. This section describes how to use the CREATE TABLE statement. The examples in this section use the sample database. To try the examples, run Interactive SQL and connect to the sample database with user ID

DBA

SQL

For information on connecting to the sample database from Interactive SQL, see Connecting to the sample database from 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.

Example

The following statement creates a new 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 Interactive SQL 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 CHARACTER string containing up to 20 characters, and so on.

• The phrase NOT NULL after their data types indicate that all columns are mandatory.

• In general, you would not create a table that has no primary key. Creating primary keys is dealt with separately, below.

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

For a full description of the CREATE TABLE statement, see CREATE TABLE statement . For information about building constraints into table definitions using CREATE TABLE, see Ensuring Data Integrity .

Altering tables

This section describes how to change the structure of a table using the ALTER TABLE statement.

Example 1

The following command adds a column to the skill table to allow space for an optional description of the skill:

ALTER TABLE skill
ADD skill_description CHAR( 254 )

This statement adds a column called skill_description that holds up to a few sentences describing the skill.

Example 2

You can also modify column attributes with the ALTER TABLE statement. The following statement shortens the skill_description column of the sample database from a maximum of 254 characters to a maximum of 80:

ALTER TABLE skill
MODIFY skill_description CHAR( 80 )

Any current entries that are longer than 80 characters are trimmed to conform to the 80-character limit, and a warning appears.

Example 3

The following statement changes the name of the skill_type column to classification:

ALTER TABLE skill
RENAME skill_type TO classification

Example 4

The following statement deletes the classification column.

ALTER TABLE skill
DROP classification

Example 5

As a final example, the following statement changes the name of the entire table:

ALTER TABLE skill
RENAME qualification

These examples show how to change the structure of the database. The ALTER TABLE statement can change just about anything pertaining to a table--you can use it to add or delete foreign keys, change columns from one type to another, and so on. In all these cases, once you make the change, stored procedures, views and any other item referring to this column will no longer work.

For a complete description of the ALTER TABLE command, see ALTER TABLE statement . For information about building constraints into table definitions using ALTER TABLE, see Ensuring Data Integrity .

Altering tables in Sybase Central

The property sheets for tables and columns display all the table or column attributes. You can alter a table definition in Sybase Central by displaying the property sheet for the table or column you wish to change, altering the property, and clicking OK to commit the change.

Deleting tables

The following DROP TABLE command deletes all the records in the skill table and then removes the definition of the skill table from the database

DROP TABLE skill

Like the CREATE statement, the DROP statement automatically executes a COMMIT statement before and after dropping the table. This makes permanent all changes to the database since the last COMMIT or ROLLBACK. The drop statement also drops all indexes on the table.

For a full description of the DROP statement, see DROP statement .

Connect to the database.

Click the Tables folder for that database.

Right-click the table you wish to delete, and select Delete from the pop-up menu.

Creating primary and foreign keys

The CREATE TABLE and ALTER TABLE statements allow you to set many table attributes, including column constraints and checks. This section shows how to set table attributes using the primary and foreign keys as an example.

Creating a primary key

The following statement creates the same skill table as before, except that it adds a primary key:

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

The primary key values must be unique for each row in the table which, in this case, means that you cannot have more than one row with a given skill_id. Each row in a table is uniquely identified by its primary key.

Columns in the primary key cannot contain NULL values. You must specify NOT NULL on the column in the primary key.

Creating a primary key in Sybase Central

Connect to the database.

Click the Tables folder for that database.

Right-click the table you wish to modify, and select Properties from the pop-up menu to display its property sheet.

Click the Columns tab, select the column name, and either click Add to Key or Remove from Key.

For more information, see the Sybase Central online Help.

Column order in multi-column primary keys

Primary key column order is based on the order of the columns during table creation. It is not based on the order of the columns as specified in the primary key declaration.

Creating foreign keys

You can create a table named emp_skill, which holds a description of each employee's skill level for each skill in which they are qualified, as follows:

CREATE TABLE emp_skill(
emp_id INTEGER NOT NULL,
skill_id INTEGER NOT NULL,
"skill level" INTEGER NOT NULL,
PRIMARY KEY( emp_id, skill_id ),
FOREIGN KEY REFERENCES employee,
FOREIGN KEY REFERENCES skill
)

The emp_skill table definition has a primary key that consists of two columns: the emp_id column and the skill_id column. An employee may have more than one skill, and so appear in several rows, and several employees may possess a given skill, so that the skill_id may appear several times. However, there may be no more than one entry for a given employee and skill combination.

The emp_skill table also has two foreign keys. The foreign key entries indicate that the emp_id column must contain a valid employee number from the employee table, and that the skill_id must contain a valid entry from the skill table.

A table can only have one primary key defined, but it may have as many foreign keys as necessary.

For more information about valid strings, see Strings .

For more information about valid identifiers, see Identifiers .

Creating a foreign key in Sybase Central

Each foreign key relationship lists a primary key in one column to a column in another table, which becomes the foreign key.

Connect to the database.

Click the Tables folder for that database.

Click the table holding the primary key, and drag it to the foreign key table.

When the primary key table is dropped on the foreign key table, the Foreign Key Wizard appears, and leads you through the process of creating the foreign key.

For more information, see the Sybase Central online Help.

Table information in the system tables

All the information about tables in a database appears in the system tables. The information is distributed among several tables.

For more information, see System Tables .

You can use Sybase Central or Interactive SQL to browse the information in these tables. Type the following command in the Interactive SQL command window to see all the columns in the SYS.SYSTABLE table:

SELECT *
FROM SYS.SYSTABLE

Connect to the database.

Right-click the database, and select Filter Objects from the pop-up menu.

Select SYS and OK.

When you view the database tables or views with Show System Objects checked, the system tables or views are also shown.