Modifying Table and Computed Column ( Interbase Tutorial # 6 )

You can modify your table using ALTER TABLE statement. With this statement you can add a columns into your table or you can delete a column as well. The syntax of this statement is :

For adding a column :

    ALTER TABLE TableName ADD columnname datatype

For dropping a column :

    ALTER TABLE TableName DROP columnname

Example 1 : in ‘employee’ table, we have four columns : id, name, age and phonenumber. We’re going to delete age column and add a column named bithdate. 
First we delete age column using this statement :

    Alter table employee drop age

Age column has been dropped. Now we’re adding birthdate column using this statement :

    Alter table employee add birthdate date

We use date data type for birtdate column. Now we have four column in ‘employee’ table : id, name, phonenumber, birthdate.

To see the changes we've made in 'employee' table, choose Metadata - Extract Table. And the output is :

/* Extract Table EMPLOYEE */

/* Table: EMPLOYEE, Owner: NEW_USER */
CREATE TABLE EMPLOYEE (ID INTEGER NOT NULL,
  NAME VARCHAR(30) NOT NULL,
  PHONENUMBER VARCHAR(12) NOT NULL,
  BIRTHDATE DATE);

Computed column

We can also have a column that has a value based on the values of other columns in the table. The syntax to create a computed column is :

   ColumnName COMPUTED BY ( arithmetic operation ) 

Example 2 : in ‘employee’ table we’re going to add three columns : salary, bonus, totalsalary. And the totalsalary column’s value = salary column’s value + bonus column’s value.

The statement will be like this :

    Alter table employee add salary integer

    Alter table employee add bonus integer

    Alter table employee add totalsalary computed by (salary+bonus)

You have to execute the statements above one by one in the InterBase Interactive SQL window. Now we have ‘employee’ table which has seven columns : id, name, phonenumber, birthdate, salary, bonus and totalsalary.
To see what we have in mydata.gdb database choose Metadata – Extract Database. And the output is :

/* Extract Database D:\mydata\MYDATA.GDB */
CREATE DATABASE "D:\mydata\MYDATA.GDB" PAGE_SIZE 1024 
;

/* Domain definitions */
CREATE DOMAIN STRING30 AS VARCHAR(30);
CREATE DOMAIN STRING12 AS VARCHAR(12);
CREATE DOMAIN DOMAIN_ID AS INTEGER
  check(value>0);
CREATE DOMAIN DOMAIN_AGE AS INTEGER

  check(value>18 and value<55);

Go to Previous Tutorial or Next Tutorial