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 is the name of your database, as assigned by CCST. */

1 > use ieem230
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.

More Examples:


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 set quoted_identifier on. 

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.

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. 

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:
1. Notice the different datatypes: int, float, char, smallint, varchar.
2. 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 ot the default, as in the case of attribute qty.
3. There is one referential constraint on the attribute title_id. It refers to the attribute title_id in a table called titles. Note: you must assign names to each of your constraints. Please use unique names fo ryour constraints in different tables.
4. A table level constraint, called salesdet_constr is also specified, which shows how to set a foreign key.
5. This example shows that it is possible to with NO PRIMARY KEY specified. In general, I suggest that you SHOULD specify a primary key for each table. 

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)
NOTES:
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.
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)

Almost all of my examples have been taken from the excellent Online Sybase Manuals.
I suggest that you also read the details of the create table and other commands from the manuals.