4. Operations on Tables

You might have noticed that we keep on making new tables whenever we are introducing a new concept. This has had the not-so desirable effect of populating our database with many similar tables. We will now go about deleting unneeded tables and modifying existing ones to suit our needs.

4.1 Dropping Tables

The deletion of tables in SQL is achieved through the DROP TABLE command. We will now drop any superfluous tables we have created during the previous lessons.

1 DROP TABLE proglang_tbl;
2 
3 DROP TABLE proglang_tblcopy;
4 
5 DROP TABLE proglang_tbltmp;

4.2 Creating new tables from existing tables

You might have noticed that we have dropped the proglang_tbl table and we now have with us only the proglang_tbluk table which has all the necessary constraints and fields. The latter’s name was chosen when we were discussing the unique key constraint, but it now seems logical to migrate this table structure (and any corresponding data) back to the name proglang_tbl. We achieve this by creating a copy of the table using a combination of both CREATE TABLE and SELECT commands and learn a new clause AS.

1 CREATE TABLE <New Table> AS SELECT <Selection> FROM <Old Table>;

Since our proglang_tbluk contains no records, we will push some sample data in it so that we can later verify whether the records themselves got copied or not. Notice that we would have to give the field names explicitly, else the second row (which contains no standard field value) would give an error similar to ‘number of target columns must equal the number of specified values’ in Ingres.

1 INSERT INTO proglang_tbluk (id, language, author, year, standard)
2 VALUES (1, 'Prolog', 'Colmerauer', '1972', 'ISO');
3 
4 INSERT INTO proglang_tbluk (id, language, author, year)
5 VALUES (2, 'Perl', 'Wall', '1987');
6 
7 INSERT INTO proglang_tbluk (id, year, standard, language, author)
8 VALUES (3, '1964', 'ANSI', 'APL', 'Iverson');

To create an exact copy of the existing table, we use the same selection criteria as we have seen before - * (star). This will select all the fields from the existing table and create the new table with them alongwith any records. It is possible to use only a subset of fields from the old table by modifying the selection criteria and we will see this later.

1 CREATE TABLE proglang_tbl AS SELECT * FROM proglang_tbluk;

We now run a simple SELECT query to see whether our objective was achieved or not.

1 SELECT * FROM proglang_tbl;

4.3 Modifying tables

After a table has been created, you can still modify its structure using the ALTER TABLE command. What we mean by modify is that you can change field types, sizes, even add or delete columns. There are some rules you have to abide by while altering a table, but for now we will see a simple example to modify the field author for the proglang_tbl table.

1 ALTER TABLE <Table name> <Operation> <Field with clauses>;

We already know that we are going to operate on the proglang_tbl table and the field we wish to modify is author which should now hold 30 characters instead of 25. The operation to choose in this case is ALTER which would modify our existing field.

1 ALTER TABLE proglang_tbl ALTER author varchar(30);

4.4 Verifying the result in Ingres

While one option to verify the result of our ALTER TABLE command is to run an INSERT statement with the author’s name greater than 25 characters and verify that we get no errors back, it is a tedious process. In Ingres specifically, we can look at the Ingres Visual DBA application to check the columns tab in the testdb database. However, another way to verify the same using a console tool is the isql command line tool available through the Ingres Command Prompt we used earlier for database creation.

To launch isql (which stands for Interactive SQL) using the Ingres command prompt we type:

1 isql testdb

The first argument we write is the database we wish to connect to. The result of running this command is an interactive console window where you would be able to write SQL statements and verify the results much like Visual SQL. The difference between the two (other than the obvious differences in the user interface) is that isql allows you access to the HELP command, which is what we will be using to verify the result of our ALTER TABLE statement. In the interaction window that opens up, we write the HELP command as below and the subsequent box shows the output of the command.

HELP TABLE proglang_tbl;

 1 Name:                 proglang_tbl
 2 Owner:                rahulb
 3 Created:              20-feb-2012 17:04:28     
 4 Location:             ii_database
 5 Type:                 user table
 6 Version:              II10.0
 7 Page size:            8192
 8 Cache priority:       0
 9 Alter table version:  4
10 Alter table totwidth: 76
11 Row width:            76
12 Number of rows:       3
13 Storage structure:    heap
14 Compression:          none
15 Duplicate Rows:       allowed
16 Number of pages:      3
17 Overflow data pages:  0
18 Journaling:           enabled after the next checkpoint
19 Base table for view:  no
20 Permissions:          none
21 Integrities:          none
22 Optimizer statistics: none
23 
24 Column Information:
25 
26 |Column Name |Type    |Length |Nulls |Defaults |Key Seq |
27 |------------|--------|-------|------|---------|--------|
28 |id          |integer |4      |no    |no       |        |
29 |language    |varchar |20     |no    |no       |        |
30 |author      |varchar |30     |yes   |null     |        |
31 |year        |integer |4      |no    |no       |        |
32 |standard    |varchar |10     |yes   |null     |        |
33 
34 Secondary indexes:    none

While there is a lot of information in the result, we are currently interested in the Column Information section which now displays the new length of the author field, i.e. 30. But it is also important to note that our ALTER TABLE statement just removed the not-null constraint from the field. To retain the same, we would have to specify the constraint in the alter command since the default behavior is to allow NULL values.

4.5 Verifying the result in other DBMS’s

The HELP command we just saw is specific to the Ingres RDBMS, it is not a part of the SQL standard. To achieve the same objective on a different RDBMS like Oracle, you are provided with the DESCRIBE command which allows you to view a table definition. While the information this command show may vary from one DBMS to another, they at least show the field name, its data type and whether or not NULL values are allowed for the particular field. The general synatax of the command is given below.

1 DESCRIBE <table name>;

4.6 Showing table information in SQLite

SQLite as of the writing of this text does not support modification to column sizes in a table using ALTER TABLE. It does however allow you to view table and column information.

SQLite has it’s own special dot syntax commands which allow certain useful database management tasks. We have already seen the .open command used to create and open a database. Similarly we can use the .schema command to get table information.

1 sqlite> .schema proglang_tbl
2 
3 CREATE TABLE proglang_tbl (
4 id INTEGER NOT NULL PRIMARY KEY,
5 language VARCHAR(20) NOT NULL UNIQUE,
6 author VARCHAR(25) NOT NULL,
7 year INTEGER NOT NULL,
8 standard VARCHAR(10) NULL);