Sybase Introduction: Creating Tables

The `create table` command

You know that all information in your DB is stored in tables. Thus creating tables is the first important thing you must learn. We have seen in the class how a good DB must be designed. The following things must be carefully considered:
1. What is the ER diagram for the mini-world ?
2. How can we use the ER diagram to get the DB Schema design ?
3. How do we assign the various constraints in the schema ?

The above steps should lead us to a DB design which has the following information:
1. Names of all the tables we need.
2. For each table, what are all the atributes ?
3. For each attribute, what are the domain constraints ?
4. In each table, what is the primary key ?
5. What are all the referential constraints (recall: Foreign keys !)

And of course, we need to know how we can specify all these in SYBASE !

All table creation and constraint specification is done when you define the table. The table definition is always done by a single command: The create table command.

The following examples will demonstrate the mechanism to perform all the above tasks using the create table command.

The following all assumes you are logged in to the SYBASE server using the unix command:
unix% isql -SSYB_TEACHING -U<your-login-name>

The simplest table

/* ieem230_db is the name of your database, as assigned by CCST. */

1 > use ieem230_db
2 > go

1 > create table student (
2 > name char(10),
3 > SID char(10))
4 > go

NOTES:
This is the simplest way to use the create table command. For each attirbute, you must specify the datatype . Data types can be fixed length character strings ( char(10) is a string of 10 characters), variable length chararcter strings ( varchar(80) is a variable length string of up to 80 characters), integers (int), dates/times (datetime), binary (bit), currency-amounts ( money) etc.. For more information, look at the Sybase Online Manuals. Remember to search for Transact-SQL User's guide to get the manual pages.

Interacting with SYBASE through script files

By now, you must have noticed that it is quite inconvenient to type such long commands directly into SYBASE. Especially if you are typing on many lines, and notice a typing error in the previous line. The best way to execute any command in SYBASE is by the use of script files . Here is an example of such a script file.

Note that you may enter MANY sybase commands in the same file -- as long as you type the "go" on a new line following the completion of each command.

It is IMPORTANT to have the ':' character in the first line !

In unix, use your editor (e.g. pico) to create such a script file, make the file executable, and execute it to run the sybase command.

More Examples

You may choose any name for tables and attributes. However, SYBASE also uses some words as names of its functions. Such words are called reserved words. If you must use a reserved word of sybase as the name of an atttribute or table, you must "quote" it, as shown in the following example.


create table "compute" 

("max" int,"min" int, "total score" int)

NOTES:
Creates the compute table. The table name and the column names max and min are enclosed in double quotes because they are reserved words.
The total score column name is enclosed in double quotes because it contains an embedded blank. Before creating this table, you must execute the following command:

set quoted_identifier on go

In the previous examples, we created tables without specifying the primary key. SYBASE allows you to do so. In reality, each table has a hidden primary key, called the IDENTITY column, created by default. If no primary key is specified, this column is used by SYBASE as the key.

But we should define the primary key for each of our tables ! Here is an example of how to do so.

create table sales
(stor_id char(4) not null,
ord_num varchar(20) not null,
date datetime not null,
primary key clustered (stor_id, ord_num))

NOTES:
Gives an example of how to set a primary key in a table. The keyword clustered is needed since the primary key has more than one attribute.

If the primary key has only one attribute, you don't need the word "clustered" as seen in some examples later.

Here is an example to show how to specify Foreign keys and other references.

create table salesdetail 
(stor_id char(4) not null,
ord_num varchar(20) not null,
title_id int not null references titles(title_id),
qty smallint default 0 not null,
discount float not null,


constraint salesdet_constr
	foreign key (stor_id, ord_num) references sales(stor_id, ord_num))

NOTES:
This example has many interesting things to note:

Notice the different datatypes: int, float, char, smallint, varchar.
Notice that you can specify default values on attributes (that is, if a record is created without this value specified, then the value is set to the default, as in the case of attribute qty.
There is one referential constraint on the attribute title_id. It refers to the attribute title_id in a table called titles. Such a constraint may be set if the attribute being referred (title_id) is not the primary key for the referred table (titles).
IMPORTANT : Each constraint must have a unique name. If you do not specify it, SYBASE will create a new name for it.
We shall only use referential constraints that are foreign keys. This is shown in our example above (constriant name: salesdet_constr ). Please use unique names for your constraints in different tables.

Here is an example of specifying a domain constraint. In this case, we set a constraint that the value of pub_id must always be one of the values specified.

create table publishers 
(pub_id char(4) not null check (pub_id in ("1389", "0736", "0877", "1622", "1756")),
pub_name varchar(40) null,
city varchar(20) null,
state char(2) null,

primary key (pub_id))

NOTES:
There are other ways of specifying domain constraints, using the following:
check ( pub_id like "99[0-9][1-5]")
which means pub_id can have four characters: the first two are "9" and "9", the third can be any number from "0" to "9", and the fourth a number from "1" to "5".
check ( pub_id like "99??" or pub_id like "99x[A-Z]")
Where we see that constraints can be joined using "or" and "and". The "?" character will match any character.

You can create your own datatypes (apart from the predefined ones like float, int etc.)! The command to do so is shown in the example below:

1 > sp_addtype hk_id, "char(10)", "not null"
2 > go
1 >

And then you can use it as follows:

create table people 
(name char(12) not null,
id_num hk_id,

primary key (id_num))

Exercises

In this lab and the next, we shall build the Employee-Department-Project database of the lectures in SYBASE. Use this ER diagram to design your tables. Write the corrsponding create table commands into files.

Hints: about potential problems in creating tables: (a) To create tables A( a1, a2, a3) and B( b1, b2, b3), where B[b3] is FK referencing A[a1].

THEN: you must create A before you create B.
REASON: since B refers to A, if A does not already exist, the system cannot record your requirements.

(b) To create tables A( a1, a2, a3) and B( b1, b2, b3), where B[b3] is FK referencing A[a1], and A[a3] is FK referencing B[b1].

THEN: as mentioned above, now A must be created before B,a nd B must be created before A, which is impossible. To solve this, we need to create the tables in three steps:

(i) Create Table A, without specifying the FK from B. (ii) Create Table B. You can specify the FK from A now, since A is created. (iii) Alter Table A, and add a constriant specifying the FK A[a3] --> B[b1].

Syntax for the alter table command:

alter table TABLE-NAME add constraint CONSTRAINT-NAME foreign key (A_ATTRIBUTE) references TABLE( B_ATTRIBUTE)

In the above example, TABLE-NAME is A, CONSTRAINT-NAME can be any unique name, e.g. fk_Aa3_Bb1, A_ATTRIBUTE is a3, TABLE is B, B_ATTRIBUTE is b1.