SYBASE Adaptive Server Enterprise (ASE) is a relational model database server, that uses Transact-SQL (T-SQL) as the procedural language for creating, modifying and querying the Databases.

Detailed below is a short overview of how to create tables in an ASE Database Server including auto generating ID SEQUENCES, foreign keys, and unique indexes.

Basic Format

Like other SQL implementations we utilize the create table command to create a new table in the Database:

create table EX_USER (
  ID int not null,
  NAME varchar(255) not null,
  ACTIVE tinyint(1) not null,
  ROLE_ID int not null,
  USER_NAME varchar(25) not null,
  PASSWORD varchar(25) not null
)

How this statement works is that you specify the table name after the create table statement and inside the () you define the various columns in the table with the format of name type  identity, null, or not null and optional other modifiers

Identity Columns

When created database tables we often want to create identity columns that are used to designate the row of data. In most cases we also want that column’s value to automatically populate on an insert with its value. In Transact-SQL this is done by the identity keyword. In our example EX_USER table lets modify the ID column to be an identity column by replacing not null with the identity keyword.

ID int identity

Unique Indices

In addition to identifiers to identify the data row in a table we also may want to specify a column to be unique in that every value is only allowed one at a time. This is accomplished by creating a unique constraint. This can be done as a separate statement or appended to the parameters in the create table statement.

create table EX_USER (
  ID int identity,
  NAME varchar(255) not null,
  ACTIVE tinyint(1) not null,
  ROLE_ID int not null,
  USER_NAME varchar(25) not null,
  PASSWORD varchar(25) not null,
  unique nonclustered (USER_NAME)
)

The nonclustered part of the statement means do not organize the data in order of the column. If by chance you wanted the data to order itself based on the column you can use the clustered keyword.

Foreign Keys

Foreign Keys are used to define columns whose value is from another table. This guarantees that the value found in the column is a valid value in the other column, and is used for lookup tables as well as for linking data across tables. In our example lets assume that we have a lookup table called EX_RULE that contains all the available user roles. We would have our RULE_ID column link to the table by the use of the references keyword.

RULE_ID int not null references EX_RULE(ID)

References

• Sybase’s Transact-SQL User’s Guide