Sybase CREATE TABLE SQL Statement

Implementing Database in Sybase – Part 5

Division 4

Forward: In this part of the series, we look at important points in the Sybase SQL Anywhere 12 CREATE TABLE Statement.

By: Chrysanthus Date Published: 26 Aug 2012

Introduction

This is part 5 of my series, Implementing Database in Sybase. This series is part of the fourth division of my larger series, Database. I assume you have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at important points in the Sybase SQL Anywhere 12 CREATE TABLE Statement. We shall also create the tables of the example.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

CREATE TABLE SQL Statement Syntax
I will first give you the complete syntax. When you read it you will probably not understand it. I will explain the important points below.

The syntax begins with:

CREATE [ GLOBAL TEMPORARY ] TABLE [ IF NOT EXISTS ] [ owner.]table-name
( { column-definition | table-constraint | pctfree }, ... )
[ { IN | ON } dbspace-name ]
[ ENCRYPTED ]
[ ON COMMIT { DELETE | PRESERVE } ROWS
   | NOT TRANSACTIONAL ]
[ AT location-string ]
[ SHARE BY ALL ]

In the syntax, anything that is in a square bracket is optional. In the syntax, | means and/or, that is you can type what is on its left and or what is its the right. The parentheses in the syntax should be typed.

The syntax for the column-definition is:

column-name data-type
[ COMPRESSED ]
[ INLINE { inline-length | USE DEFAULT } ]
[ PREFIX { prefix-length | USE DEFAULT } ]
[ [ NO ] INDEX ]
[ [ NOT ] NULL ]
[ DEFAULT default-value | IDENTITY ]
[ column-constraint ... ]

The syntax for default-value in the column-definition is:

special-value
| string
| global variable
| [ - ] number
| ( constant-expression )
| built-in-function( constant-expression )
| AUTOINCREMENT
| CURRENT DATABASE
| CURRENT REMOTE USER
| CURRENT UTC TIMESTAMP
| GLOBAL AUTOINCREMENT [ ( partition-size ) ]
| NULL
| TIMESTAMP
| UTC TIMESTAMP
| LAST USER

The syntax for the special-value in default-value is:

CURRENT {
   DATE
   | TIME
   | TIMESTAMP
   | USER
   | PUBLISHER
   | DATABASE
   | REMOTE USER
   | UTC TIMESTAMP
}
| USER

The syntax for column-constraint in column-definition is:

[ CONSTRAINT constraint-name ] {
   UNIQUE [ CLUSTERED ]
  | PRIMARY KEY [ CLUSTERED ] [ ASC | DESC ]
  | REFERENCES table-name [ ( column-name ) ]
     [ MATCH [ UNIQUE ] { SIMPLE | FULL } ]
     [ action-list ] [ CLUSTERED ]
  | CHECK ( condition )
   }
| COMPUTE ( expression )

The table-constraint in the main syntax is:

[ CONSTRAINT constraint-name ] {
     UNIQUE [ CLUSTERED ] ( column-name [ ASC | DESC ], ... )
  | PRIMARY KEY [ CLUSTERED ] ( column-name [ ASC | DESC ], ... )
  | CHECK ( condition )
  | foreign-key-constraint
}

The syntax of the foreign-key-constraint in the table-constraint is:

[ NOT NULL ] FOREIGN KEY [ role-name ]
   [ ( column-name [ ASC | DESC ], ... ) ]
   REFERENCES table-name
   [ ( column-name, ... ) ]
   [ MATCH [ UNIQUE] { SIMPLE | FULL } ]
   [ action-list ] [ CHECK ON COMMIT ] [ CLUSTERED ] [ FOR OLAP WORKLOAD ]

The syntax of the action-list of the foreign-key-constraint is:

[ ON UPDATE action ]
[ ON DELETE action ]

The syntax of the action in the action-list is:

CASCADE
| SET NULL
| SET DEFAULT
| RESTRICT

The syntax of the location-string in the main syntax is:

remote-server-name.[db-name].[owner].object-name
| remote-server-name;[db-name];[owner];object-name

The syntax of pctfree in the main syntax is:

PCTFREE percent-free-space

The Syntax of percent-free-space in pctfree is:

Integer

We have just seen the complete syntax. You probably have not understood it. I will explain only the important points. Consult some other document for the other points.

Referential Integrity
The referential integrity is enforced by the complete Foreign Key line in the SQL statement when creating the table. The column the foreign key is referencing in the other table has to be either a primary key or a unique column.

The Check Constraint
The CHECK constraint whose syntax is:

CHECK ( condition )

is used to restrict values in a column within a particular set. For example, you can use it to insist that no price input in the price column should be less than zero. The CHECK constraint is coded inside the SQL statement. In the next part of the series we shall learn how to write the condition in the syntax, “CHECK (condition)”. The CHECK constraint can be implemented in the column-definition or in the table-constraint section of the complete SQL syntax.

Comment
You can type a comment in your SQL statement. A comment is used to help you remember what you have coded. It begins with two consecutive dashes, i.e. --, until you press the Enter key. Here is an example I got from the manual:

CREATE TABLE Orders (
   order_num INTEGER NOT NULL PRIMARY KEY,
   date_ordered DATE,
   name CHAR(80)
);

CREATE TABLE Order_item (
   order_num INTEGER NOT NULL,
   item_num SMALLINT NOT NULL,
   PRIMARY KEY ( order_num, item_num ),
   -- When an order is deleted, delete all of its
   -- items.
   FOREIGN KEY ( order_num )
   REFERENCES Orders ( order_num )
   ON DELETE CASCADE
);

There are two SQL statements here. The second SQL statement has two comment lines, where a sentence starts in one comment line and ends in the other comment line.

Indexes
The guidelines for creating indexes in a table are:

- Define a unique index on a primary key.
- Index all foreign keys.
- Want to insist on unique values for a column, index that column.
- Index frequently searched columns.
- Index columns that frequently determine the sort order.
- The longer the table, the likelihood that it should have indexes.

From the way Sybase SQL Anywhere 12 is designed, the first two points above are taken care for you as follows: When you declare columns as primary keys in the CREATE TABLE SQL statement, the indexes are automatically created for the primary key columns, and the uniqueness of the primary key is made. When you enforced referential integrity, indexes are enforced for the foreign keys. Primary keys and foreign keys are frequently searched columns. So many of the columns for point 4 above are taken care of. However, if you believe a column in a table that is not a primary key or a foreign key will be frequently searched, then index the column. The last three points are not taken care of by Sybase SQL Anywhere 12, while the first three points are taken care of.

With Sybase SQL Anywhere 12, if you want a column to be unique, you have to use the UNIQUE constraint of the column-definition. You can then index the column if you think it will be frequently searched.

I have explained the rest of the important points in the previous part of the series.

Creating Tables of the Example
We continue with the wholesale example. Carry out the following instructions:

- Open the command prompt window.
- Type the following and press Enter to go to the root directory:

                cd c:\

- Type the following command and press Enter. If it says the database (file) already exist, allow things like that. If the path “c:\wholesale” does not exist, create it first.

                dbinit c:\wholesale\wholesale.db

- Start the server and the database with the following command, pressing Enter after typing.

                dbeng12 -n wholesalesrv c:\wholesale\wholesale.db

- Start the window tool, Interactive SQL and connect to the database by typing the following command and pressing the Enter key.

dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"

- There are 8 tables whose creation SQL statements are in the previous part of the series. Type each of the complete statements in the SQL Statements pane in the Interactive SQL window. After typing click SQL>>Execute (or the Play Button) from the main menu of the Interactive SQL window, to execute. If that goes well, you will see some result in the Results pane, and that means the table structure has been saved in the database file. If there is an error, a new window will appear showing the error. In that case correct the error and re-execute. (keyword typed correctly, changes color in Interactive SQL). Do this for all the 8 tables.
- Close the Interactive SQL window; it may ask you if you want to save the SQL statements; click No. This is because the tables have automatically been saved and you do not have to save the SQL statement text in a different file for this project.
- Close the connection, stop the database and stop the server by typing the following command in the command prompt and pressing Enter:

            dbstop wholesalesrv

If the command prompt ask you to type Y or N, type y and press the Enter key again.

We created the database and we have just created its tables. Let us end there. We continue in the next part of the series.

Chrys

Broad Network

Related Articles

Sybase CREATE TABLE SQL Statement

Implementing Database in Sybase – Part 5

Division 4

Introduction

Related Courses

Comments