Previous Topic

Next Topic

Creating Tables

The CREATE TABLE statement allows creation of a new table and definition of its columns and their data types in an existing database. A sample code using CREATE TABLE statement in an ESQL program is shown below:

Example Creating Tables


...
/* connect to a default database */
EXEC SQL CONNECT TO DEFAULT ;

/* create a table named customer in the database */
EXEC SQL
     CREATE TABLE customer (
          CUST_NO INTEGER NOT NULL,
          cstname CHAR (30),
          street  CHAR (30),
          city    CHAR (20),
          state   CHAR (2)
      ) ;
if (sqlca.sqlcode)
{
   fprintf (stderr,
   "Create table statement failed (%ld : %s) \n",
   sqlca.sqlcode, sqlca.sqlerrm);
   EXEC SQL ROLLBACK WORK ;
   EXEC SQL DISCONNECT DEFAULT ;
   exit (1);
}

/* Commit changes */
EXEC SQL COMMIT WORK ;

printf ("Table customer created. \n");

/* Disconnect from the database */
EXEC SQL DISCONNECT DEFAULT ;
...

The CREATE TABLE statement shown above specifies the table name, customer, for the table to be created. In addition, the statement specifies the column definitions such as the column name and the column type. The integer column, CUST_NO, specified as NOT NULL indicates that no row in the table customer can have a NULL value in the column, CUST_NO.

The CREATE TABLE statement allows specification of DEFAULT clause along with a column definition. The DEFAULT clause specifies the default value to be used for a column, if the value for the same is not supplied while inserting the row. The following CREATE TABLE statement shows the usage of the DEFAULT clause:

CREATE TABLE employee (
     empno      INTEGER NOT NULL,
     deptno     INTEGER DEFAULT 10
) ;

In this example, a default value of 10 is specified for the column deptno.