Table Editor Concepts

Table Editor Concepts

This section contains the following topics:

•

•

Table Editor Pages

•

•

Table Editor is one of the editor windows within PSQL Control Center (PCC). Table Editor is a special type of editor that contains multiple pages. The pages are represented by tabs across the bottom of the editor. The editor allows you to add, delete, or change the characteristics of columns within a table. The table may be one newly created or an existing table that you want to edit.

To modify tables with Table Editor, you must have full administrator rights on the machine on which the database engine is running even if you are a member of the Pervasive_Admin group. See Granting Administrative Rights for the Database Engine and Database Security in Advanced Operations Guide.

Caution: Backup all your data definition files (DDFs) and data files before you perform functions through Table Editor. This tool gives you the ability to modify your database table definitions and data. If you inadvertently set the options incorrectly or enter incorrect data, you could change your files in an irreversible manner. Full recovery is possible if you have performed a backup.

Table Editor Pages

Table Editor contains the following pages that you use as work areas:

•

•

•

•

You select a page by clicking its page name tab.

Note: Save your changes before switching pages.

The Columns page lets you add, delete, modify columns, and set primary keys. See Columns Page for a description of the areas on the Columns page. See Columns Tasks for the tasks that you perform on the page.

Refer to PSQL Supported Data Types in SQL Engine Reference for a list of the data types supported by the database engine. You may use any data types listed in PSQL Supported Data Types and that appear in the “Type” selection list on the Columns page in Table Editor.

The ability to modify the null attribute of a column is subject to the following restrictions:

•

The target column cannot have a PRIMARY/FOREIGN KEY constraint defined on it.

•

If converting the old type to the new type causes an overflow (arithmetic or size), the ALTER TABLE operation is aborted.

•

If a nullable column contains NULL values, the column cannot be changed to a non-nullable column.

If you must change the data type of a key column, you can do so by deleting the index key, changing the data type, and re-adding the key. Keep in mind that you must ensure that all associated index key columns in the database remain synchronized.

For example, if you have a primary index key in table T1 that is referenced by foreign keys in tables T2 and T3, you must first delete the foreign keys. Then you delete the primary key and change all three columns to the same data type and size. Finally, you must re-add the primary key and then the foreign keys.

For additional information on nulls, see the following:

•

Rebuild Utility Concepts in Advanced Operations Guide

•

INSERT in SQL Engine Reference

•

Null Value in PSQL Programmer's Guide, which is part of the PSQL Software Developer’s Kit (SDK).

The Indexes page lets you add and modify indexes and index segments. See Indexes Tasks for the tasks that you perform on the page.

Foreign Keys Page

The Foreign Keys page lets you add and modify foreign keys. See Foreign Keys Tasks for the tasks that you perform on the page.

The SQL View page lets you view the CREATE TABLE and ALTER TABLE statements that apply to the table. Note that SQL View is display only. You cannot modify the SQL statements but you can copy them. See SQL View Tasks for the tasks that you perform on the page.

The SQL View page reflects changes made on the other pages as explained below.

CREATE Statements

The CREATE TABLE statement shows the SQL used to create the table. For a new table, one that has not yet been saved, the CREATE TABLE statement reflects the SQL used to implement edits from the other three tabbed dialogs. For example, if you were to create a new table named “MyNewTable,” SQL View initially shows the following CREATE statement:

CREATE TABLE MyNewTable(

If you were to add two CHAR columns to the new table on the Columns page, SQL View reflects this in the CREATE statement:

CREATE TABLE MyNewTable(

"FirstName" CHAR(20),

"LastName" CHAR(30)

If a table has been saved (already exists), the CREATE TABLE statement shows the SQL required to create the table. For example, if you were to edit the “Course” table provided with the sample database DEMODATA, SQL View shows the following CREATE statements:

CREATE TABLE Course(

"Name" CHAR(7) NOT NULL ,

"Description" CHAR(50),

"Credit_Hours" USMALLINT,

"Dept_Name" CHAR(20) NOT NULL

CREATE INDEX Course_Name ON Course("Name");

CREATE UNIQUE INDEX DeptName ON Course("Dept_Name");

ALTER Statements

When you are editing an existing table, the ALTER TABLE statements show what SQL is used to implement edits from the other three Table Editor pages. For example, suppose that you edit the “Course” table provided with the sample database DEMODATA. On the Index page, you change the sort order for the “Name” index segment from ascending to descending. SQL View shows the following ALTER statements:

DROP INDEX Course.Course_Name;

CREATE INDEX Course_Name ON Course("Name" DESC);

Saving a table clears the ALTER TABLE statements because no changes are pending.