Schema Management

CREATE TABLE

The CREATE TABLE keyword at the beginning of an SQL statement tells OpenBase that you want to create a new table. Creating new tables can also be accomplished by using the graphical tools provided with the OpenBase Manager. However, this section will give you an idea of what happens on the SQL level.

The general format of the create statement is as follows:

CREATE TABLE <Table Name> (<column name> <type> [NOT NULL] [ [ CLUSTERED] [ UNIQUE] INDEX] [ default <value> ] [REFERENCES <table>.<column>], ...)

Here is an example of creating a table called customer with fields number, name, and balance:

CREATE TABLE customer (number longlong NOT NULL UNIQUE INDEX, name char(30) NOT NULL DEFAULT yourName , balance money)

The following is another example of creating tables.

1. Create the tables. You can also do this using the OpenBase Manager. The key is to make sure you assign indexes to both sides of the relationship.

openbase 1> create table t1 (pk int unique index)
openbase 2> go
openbase 1> create table t2 (fk int index)
openbase 2> go

2. Add the data. This is the data I added to the database using the OpenBase Manager. Normally it would be better to add the references constraint *before* adding data but I am doing it this way to simulate a database with existing data.

TABLE: t1
pk
---
1
2
--

TABLE:t2
_rowid fk
---
1 2
2 1
3 1
-
----

3. Create the references constraint. Notice that you have to define all of the information about the column again. It will not replace the data, but it will essentially replace the column with one that references the PK and table t1.

openbase 1> alter table t2 add column fk int index references t1.pk
openbase 2> go

4. Finally, check to make sure if it worked. I am doing this to show what you should expect.

openbase 1> delete from t1
openbase 2> go
SQL ERROR - Data Integrity Violation: Records in the delete result
    set are referenced by other tables

The numbers that are specified for the character lengths in the above example (name char(30) means the string length is 30) will be enforced by the database server. All character lengths are assumed to be between 0 and 4096 characters. If you want to store text information longer than 4096 you will need to use an Object or TEXT column type.

Column Specifications

Column specifications within CREATE statements can include NOT NULL, INDEX, UNIQUE INDEX, and DEFAULT <value>. Each of these are described as follows:

NOT NULL
The NOT NULL tells the server that the column must always have a value. Trying to insert a row without specifying a value for this column will result in an error. Likewise, updating that column and setting it to NULL will also generate an error. The NOT NULL option provides a safeguard so that critical data is not absent.

DEFAULT <value>
DEFAULT <value> is used when you want the column to have a default value when it is not specified in an insert statement. The default value is used instead of NULL for unspecified columns. <value> can be specified as a formula similar to a derived value.

INDEX and UNIQUE INDEX
INDEX indicates whether the column should be indexed. If UNIQUE INDEX is used, the server will ensure that the values of each row are unique. In this case, inserting an existing value will cause an error.

CLUSTERED INDEX and CLUSTERED UNIQUE INDEX
Adding the CLUSTERED keyword to your index definition arranges the physical data in the table by the indexed column. This provides significant performance benefits in cases where data is accessed through a foreign key or is sorted by the specific column. By clustering the column the database is able to find records likely to be accessed together on consecutive pages. Only one column per table can be used with clustering.

DERIVED formula
The DERIVED keyword allows you to create an indexable column that is calculated. Please refer to the section below for details on this feature.

REFERENCES table.column
The REFERENCES keyword provide referential integrity checking between tables. Records in the target table will not be deleted if they are referenced by records in the table with the references clause. Deleting records that are referenced by another table will return an error.

Data Types

Following is a list of data types and their C equivalents, supported by OpenBase:

sqltable.jpg

All values may be expressed and retrieved as strings. OpenBase will automatically convert the data to the appropriate representations.

The object and text data-types are used to store BLOB (Binary Large Objects) ids for image or other binary data. Object and TEXT fields have a special mechanism that will automatically erase old BLOBs when updated with a new BLOB id. When records are deleted, the database will also automatically remove associated BLOBs.

The TEXT date-type is used for storing large amounts of text where previously the data-type OBJECT would have been used. The difference between these types is that the TEXT type applies a text encoding to the data so that it appears correctly no matter what encoding the client computer is using. The OBJECT data type stores binary data and does not try to apply a text encoding.

For efficiency reasons, Object and Text data (BLOBs) are not stored in the records themselves. Because of their potentially large size they are kept in a separate area and referenced using a BLOB id which connects the blobs to the record columns. Methods in the various OpenBase APIs available allow you to retrieve BLOB information when you need it using the BLOB id value stored in the column.

Derived Columns

Derived columns return calculated values or concatenated strings. The OpenBase implementation requires that you specify the derived column when you first create the table. To do this you would use something like the following:

CREATE TABLE ACCOUNTS (subtotal money, shipping money, tax money, total money derived subtotal + shipping + tax)

As you insert data into the ACCOUNTS table, the value for the total column is automatically calculated. Normally derived columns do not map directly to columns in the database, but they often use column values. As with regular result columns, derived columns are expressed after the SELECT keyword.

Another example of derived columns would be this:

CREATE TABLE ADDRESS (FIRSTNAME VARCHAR, LASTNAME VARCHAR, FULLNAME VARCHAR UNIQUE INDEX DERIVED FIRSTNAME+" "+LASTNAME)

Using this method ensures that only one person with the same name will be allowed in the database. When you attempt to enter the same name again you will receive an error informing you that the value of the column FULLNAME is not unique. Other database products may refer to this feature as an 'alias'.

You can also add a derived column to an already existing table using the alter table command. So if you already had a table called ADDRESS with the FIRSTNAME and LASTNAME columns, you could add the FULLNAME column like this:

ALTER TABLE ADDRESS ADD COLUMN FULLNAME VARCHAR UNIQUE INDEX DERIVED FIRSTNAME+" "+LASTNAME

This would update all existing rows in the table, and complete the FULLNAME column.

Any formula or function may be used, but subqueries are not supported.

CREATE [UNIQUE] INDEX

The CREATE INDEX and CREATE UNIQUE INDEX commands are used to create indexes on columns. Indexes are necessary to provide better performance when joining tables or searching information. The syntax is defined as follows:

CREATE [UNIQUE] INDEX <table> <column>

Here are two examples using the MOVIE database:

CREATE INDEX TALENT LAST_NAME
CREATE UNIQUE INDEX MOVIE_ROLE ROLE_NAME

CREATE CLUSTERED [UNIQUE] INDEX

Adding the CLUSTERED keyword to your index definition arranges the physical data in the table by the indexed column. This provides significant performance benefits in cases where data is accessed through a foreign key or is sorted by the specific column. By clustering the column the database is able to find records likely to be accessed together on consecutive pages.

Only one column per table can be used with clustering. Setting the clustered column replaces any previous setting.

You can also set the clustered column using the OpenBase Manager Schema Window. The popup entitled Natural Order allows you to set the clustered index.

Create Primary Keys

A primary key is a column or set of columns that uniquely identify a record. Using CREATE PRIMARY KEY you can specify which database column or group of columns should be unique.

It is extremely important that all columns in the primary key specification are indexed.

CREATE PRIMARY KEY tableName (column)

The above example specifies a single database column as the primary key. The example below specifies a multi-column key.

CREATE PRIMARY KEY tableName (col1, col2, col3)

Multi-column keys are discouraged because they are much less efficient, more complicated to use, and are not supported by some development environments, such as RADstudio.

Drop and Rename Tables

DROP TABLE

OpenBase supports dropping and renaming tables. The syntax for dropping a table is as follows:

DROP TABLE tableName

This will remove the table tableName from the database. In other cases you may want to rename a table. The following command will rename tableName to newName:

RENAME tableName newName

We recommend that where possible you use the OpenBase Manager schema editing tools for dropping and renaming tables.

Change Database Schemas

ALTER TABLE
There are two types of alter table commands, simple and complex. Simple ones do not require the database to rebuild its pages on the disk. Changing a column name, or column parameters are good examples and are fairly easy for the database to do. More complex alter table commands require the database to do more work. Examples include adding columns, removing columns or changing the type of columns. The good news is that you can include all of your changes in a single command statement to avoid making the database perform multiple passes.

To change the name of a column you can perform ALTER TABLE using the rename feature. The following command will rename the columnFIRST_NAME to FIRSTNAME.

ALTER TABLE TALENT RENAME FIRST_NAME TO FIRSTNAME

You can also change the parameters on a column, the following examples show how you can assign default values for columns and change some of the other column parameters.

Sometimes you may want to assign a default value for a previously defined column. The following example demonstrates how this is done. When the column value is not specified by subsequent inserts, the column value will be set to the default value.

ALTER TABLE MOVIE COLUMN RATING SET DEFAULT 'PG'

The following example shows how you can set the length of a character string column. The length is enforced for future inserts and updates, but altering the length will not truncate existing data.

ALTER TABLE MOVIE COLUMN TITLE SET LENGTH 50

The following example shows how to make sure required column values are always specified.

ALTER TABLE MOVIE COLUMN RATING SET NOT NULL

The following command reverses the SET NOT NULL shown above, allowing the column to contain NULL values again.

ALTER TABLE MOVIE COLUMN RATING SET NULL

The following two commands show how to create and remove indexes on a column. You can also use create index to do the same thing.

ALTER TABLE MOVIE COLUMN CATEGORY SET INDEX
ALTER TABLE MOVIE COLUMN RATING SET NO INDEX

You can also create a unique index on columns.

ALTER TABLE MOVIE COLUMN MOVIE_ID SET UNIQUE INDEX

Adding, removing and changing the type of columns are more time consuming than the ALTER TABLE commands discussed above. The reason is that they require the database to translate data between the old and new data structure.

The following example adds two columns, removes two columns, and changes the parameters for the name column. The ADD COLUMN section should be a list of fields as they would appear in a create statement. If columns listed in this section are already in the table, the characteristics of the columns are replaced and the data is converted.

ALTER TABLE MOVIE ADD COLUMN COUNTRY CHAR(20) DEFAULT USA, COUNTRY_ID INT NOT NULL, DIRECTOR VARCHAR NOT NULL INDEX DEFAULT Unknown REMOVE COLUMN RATING

When ADD COLUMN and/or REMOVE COLUMN are used, the table must be rebuilt by the server. This may take awhile if you are modifying a large table.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License