Altering and Dropping Tables in Sybase

Implementing Database in Sybase – Part 7

Forward: In this part of the series, we see how to alter and drop tables in the Sybase SQL Anywhere 12.

By: Chrysanthus Date Published: 27 Aug 2012

Introduction

This is part 7 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 see how to alter and drop tables in the Sybase SQL Anywhere 12.

We are still dealing with data definition. Remember data definition deals with creating of tables, altering tables, dropping tables, creating of indexes and giving different users, privileges to different tables. We have seen how to create tables and give them indexes. In this part of the series we shall see how to alter and drop tables. We shall see how to create user accounts (user name and password) and grant them certain permissions in the next part of the series.

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.

Altering a Table
To create a table means to create the table structure (automatically saving the table in the database file). To alter a table, means to alter the table structure. It is not anybody who has the right to alter a table. You will be able to do that now because you are the database administrator, since you have the default user ID, DBA. Since you still have the password, sql, it means anybody can do what you have the right to do. To stop others from doing what you can do you should change the password; however for the sake of simplicity, do not do that for this series. A database administrator has the rights to do anything in the database.

We shall start by looking at some examples, which alter tables. After that I will give you the complete syntax and explain how to read the complete syntax.

Example
We continue with the wholesale example.

- Type the following commands in the command prompt to start the database and server and connect to the database through Interactive SQL; when you type one command, wait for it to take effect before you type the next:

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

- We shall add the CHECK constraint, CHECK (CostPrice > 0), to the CostPrice column of the Products table. Type the following statement in the SQL Statements pane in the Interactive SQL window and then click the Play Button in the Tools bar to execute the statement.

ALTER TABLE Products ALTER CostPrice ADD CHECK (CostPrice > 0);

All SQL statements end with semicolon. If everything goes well you should see result in the Results pane, otherwise a new window will appear indicating the error.

- Let us alter the SellingPrice column in the Products table in a similar way. Erase the present statement in the SQL Statement pane and type the following; click the Play Button or SQL>>Execute from the menu, for execution:

ALTER TABLE Products ALTER SellingPrice ADD CHECK (SellingPrice > 0);

It should be executed without errors. We shall now add Total columns in the Sales and Orders table.

- Erase the present SQL statement in the SQL Statement pane. Type and execute the following command:

ALTER TABLE Sales ADD TOTAL NUMERIC(19,2);

The SQL Statement should have been executed.

- You will now do a similar thing for the Orders table. Erase what you have just typed in the SQL Statement pane. Type and execute the following:

ALTER TABLE Orders ADD TOTAL NUMERIC(19,2);

A Total column is a computed value column. Sybase SQL Anywhere 12 gives you the possibility of typing in the formula (expression) that would do the calculation and put the answer there automatically; however I will not go into that in this part of the series.

Dropping a Table
In simple terms the SQL statement syntax to drop a table is:

    DROP TABLE table-name;

You will now create a table in the database and then drop it. Dropping a table means erasing the table from the database file.

- Erase what is in the SQL Statement pane in the Interactive SQL window; type and execute the following:

CREATE TABLE Purchase (
   purchase_num INTEGER NOT NULL PRIMARY KEY,
   date_purchased DATE,
   name CHAR(80)
);

You should now have a new table called Purchase.

- Now you will delete the table you have just created and have automatically saved. Erase what you have typed in the SQL Statement pane. Type the following and execute:

DROP TABLE Purchase;

The new table purchase should have been erased.

Let us now stop the connection, stop the database and stop the server.

- Close the Interactive SQL window; if it asks you to save anything, click No.

- Type the following command in the command prompt and press the Enter key to do the three stops; if it asks you to type Y, type it and press the Enter key.

            dbstop wholesalesrv

Complete Syntax to Alter a Table
The complete syntax of the SQL Statement to alter a table is given below; glance through it and read the explanation below it:

ALTER TABLE [owner.]table-name { alter-clause, ... }

alter-clause :
ADD create-clause
| ALTER column-name column-alteration
| ALTER [ CONSTRAINT constraint-name ] CHECK ( condition )
| DROP drop-object
| RENAME rename-object
| table-alteration

create-clause :
column-name [ AS ] column-data-type [ new-column-attribute ... ]
| table-constraint
| PCTFREE integer

column-alteration :
{ column-data-type | alterable-column-attribute } [ alterable-column-attribute ... ]
| SET COMPUTE ( compute-expression )
| ADD [ constraint-name ] CHECK ( condition )
| DROP { DEFAULT | COMPUTE | CHECK | CONSTRAINT constraint-name }

drop-object :
column-name
| CHECK
| CONSTRAINT constraint-name
| UNIQUE [ CLUSTERED ] ( index-columns-list )
| FOREIGN KEY fkey-name
| PRIMARY KEY

rename-object :
new-table-name
| column-name TO new-column-name
| CONSTRAINT constraint-name TO new-constraint-name

table-alteration :
PCTFREE DEFAULT
| [ NOT ] ENCRYPTED

new-column-attribute :
NULL
| DEFAULT default-value
| COMPRESSED
| INLINE { inline-length | USE DEFAULT }
| PREFIX { prefix-length | USE DEFAULT }
| [ NO ] INDEX
| IDENTITY
| COMPUTE ( expression )
| column-constraint

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

column-constraint :
[ CONSTRAINT constraint-name ] {
   CHECK ( condition )
   | UNIQUE [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
   | PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
   | REFERENCES table-name [ ( column-name ) ]
      [ MATCH [ UNIQUE ] { SIMPLE | FULL } ]
      [ actions ][ CLUSTERED | NONCLUSTERED ]
   | NOT NULL
  }

alterable-column-attribute :
[ NOT ] NULL
| DEFAULT default-value
| [ CONSTRAINT constraint-name ] CHECK { NULL | ( condition ) }
| [ NOT ] COMPRESSED
| INLINE { inline-length | USE DEFAULT }
| PREFIX { prefix-length | USE DEFAULT }
| [ NO ] INDEX

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

special-value :
CURRENT {
   DATABASE
   | DATE
   | REMOTE USER
   | TIME
   | TIMESTAMP
   | UTC TIMESTAMP
   | USER
   | PUBLISHER }

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

actions :
[ ON UPDATE action ] [ ON DELETE action ]

action :
CASCADE | SET NULL | SET DEFAULT | RESTRICT

[Collapse/expand section]  Syntax 2 - Disabling view dependencies

Explaining the Syntax
I will just explain how I came about the following statement:

    ALTER TABLE Products ALTER CostPrice ADD CHECK (CostPrice > 0);

I used this statement above to add a CHECK constraint to the CostPrice column of the Products table.

The complete syntax is in sections. The lower you go down the syntax the more details you find about a word or phrase above in the syntax. In the syntax, | means and/or; anything in square brackets is optional; consider the word, expression, as a column name or value; phrases or words in lower case should be replaced by names of your choice in your SQL statement.

The first line in the syntax is:

    ALTER TABLE [owner.]table-name { alter-clause, ... }

From this first line, I got

    ALTER TABLE Products

for my own statement and I then proceeded to the details of, alter-clause, in the syntax. From the second line of the details of, alter clause, I got

    ALTER CostPrice

for my statement. The second line of the details of, alter clause, ends with, column-alteration. I then went to the details of, column-alteration, and from the third line of the details of, column-alteration, I got,

ADD CHECK (CostPrice > 0)

for my statement.

That is the kind of procedure you should follow to determine your own Altering SQL statements. You should apply the same reasoning to understand other syntaxes in Sybase, such as the CREATE TABLE syntax we saw in one of the previous parts of the series.

Time to take a break. Let us stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Altering and Dropping Tables in Sybase

Implementing Database in Sybase – Part 7

Introduction

Related Courses

Comments