MDBitz Development Resources

Over the lifespan of a database driven application you may find that your existing database schema is not sufficient for new functionality. To maintain the integrity of your existing data Sybase and other SQL Servers enable administrators to modify existing tables by adding columns and constraints.

SQL Format

The basic format for the add column script is as follows

ALTER TABLE <em>table_name</em>
     ADD <em>column_name</em> <em>datatype DEFAULT null | not null
</em>

How the script works is that you specify the name of the table you want to modify then use the add clause to define the column you want to add to the table. In the add clause you specify the column name, type, null or not null, and if not null the default value to insert into existing data rows in the table.

Example

Lets assume we are starting with the following database table USERS

Assuming the above table and data lets add a column called ACTIVE which will tell us if that user is active or inactive.

ALTER TABLE USERS
    ADD ACTIVE TINYINT 1 not null

After running this script we will have the following table with the ACTIVE column for all existing records having a value of 1

Resources

• Sybase – Transact-SQL Add Column Manual

There are a few methods available to the Sybase Transact-SQL developer who needs to obtain the next primary key for the next row to be inserted. If the primary column is an identity column then the next_identity function can be used. The proper syntax is:

SELECT next_identity( "TABLE_NAME" )

If however you have not used the identity column then you can obtain the next ID to be used for INT type columns by getting the highest ID currenlty being used than adding 1 to it. This is done by using the isnull and MAX functions.

SELECT isnull( MAX(ID), 0 ) + 1 FROM TABLE_NAME

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