Working with tables

User's Guide
PART 1. Working with Databases
CHAPTER 3. Working with Database Objects

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 The examples can be executed in Interactive SQL, but the SQL statements are independent of the administration tool you are using.

You should create command files containing the CREATE TABLE and ALTER TABLE statements that define the tables in your database. In this way, you can re-create the database when necessary.

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.

To create a table using Sybase Central:

Connect to the database.
Click the Tables folder for that database.
Double-click Add Table in the right panel.
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 and password 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.
All columns are mandatory as indicated by the phrase NOT NULL after their data types.
In general, you would not create a table that has no primary key. Creating primary keys is dealt with separately, below.

Before creating the table, all previous changes to the database are made permanent by internally executing the COMMIT statement. There is also a COMMIT after the table is created.

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

Column attributes can also be modified 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 is displayed.

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—foreign keys can be added or deleted, columns can be changed from one type to another, and so on.

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 all changes to the database since the last COMMIT or ROLLBACK permanent.

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

To drop a table in Sybase Central:

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 many attributes of tables to be set, 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 a primary key is added:

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 are not allowed to contain NULL. You must specify NOT NULL on the column in the primary key.

Creating a primary key in Sybase Central

To create 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 Info 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.

The skill level column name contains a space and is surrounded by quotation marks ("double quotes"). You can use any characters in column names and table names, but the names must be enclosed in quotation marks under the following circumstances:

If any characters other than letters, digits or the underscore are used
If the name does not begin with a letter
If the name is the same as a keyword.

Single and double quotes in SQL

Remember, in SQL:

Single quotes (apostrophes) are used to indicate strings. For example:
```
'SMITH', '100 Apple St.', '1988-1-1'.
```
Double quotes (quotation marks) are used to indicate table or column names (for example, "skill level", "emp_id", "skill_type").

To include a single quote inside a string, use two single quotes:
```
'''Plankton'', said the cat'
```

For Info For more information about using primary and foreign keys, see Ensuring Data Integrity.

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.

create a foreign key in Sybase Central:

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 is displayed, which leads you through the process of creating the foreign key.

For Info For more information, see the Sybase Central online Help.

Table information in the system tables

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

For Info 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

To display the system tables in Sybase Central:

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.