This chapter discusses how to manage tables, views, and schemas. It includes information on table limits, handling duplicate rows in tables, manipulating columns, modifying tables in various ways, and rules for updating views. This chapter also discusses synonyms, temporary tables, and comments, which are features for manipulating table data and referencing tables.
|
|
|
You can perform the following basic operations on tables:
In VDBA, you use the Tables branch for a particular database in the Database Object Manager window.
In SQL, you can accomplish these tasks using the create table, alter table, help table, modify table, and drop statements. For more information, see the SQL Reference Guide.
|
|
|
You can create a table by issuing a create table statement from any of the following tools:
For details on the create table statement, see the SQL Reference Guide.
You can also use the Tables utility to create tables. This utility lets you build and destroy tables, inspect their structure, and run queries and reports on their data.
UNIX: For a discussion of the Tables utility, see the Character-based Querying and Reporting Tools User Guide. 
VMS: All users can create tables unless explicitly restricted from doing so using a nocreate_table database grant.
|
|
|
The new table is owned by the user who creates it. The owner of the table is allowed to perform certain operations on the table that other users are not. For example, only the owner (or a user with the security privilege impersonating the owner) can alter or drop a table.
If other users need access to the table, the table owner can grant that permission using table grants. Table grants are enabling permissions—if no permission is granted, the default is to prohibit access.
|
|
|
When you create a table, it is placed in the default location designated for the database's data files, unless you specify otherwise.
|
|
|
Before using an alternate location for a table, the following requirements must be met:
|
|
|
To create a table in an alternate location, click Options in the Create Table dialog in VDBA. This opens the Options dialog, where you choose one or more alternate locations. For details, see online help.
If you specify only one location, the entire table is stored in that location. If you choose more than one location, the table spans multiple locations. For example, if you designate two locations, the table is extended over two alternate locations. As rows are added to the table, they are added to each area in alternate blocks.
The blocks are:
Windows: 16-page blocks (approximately 32 KB)
UNIX: 16-page blocks (approximately 32 KB)
VMS: 8 pages (32 disk blocks)
The table is considered out of space if the next receiving area in turn does not have a sufficient block.
Note: After creating a table, you can change its location, as described in Techniques for Moving a Table to a New Location.
|
|
|
When you create a table, journaling can be enabled by default, depending on the setting of default_journaling in the Ingres DBMS Server class your session is attached to.
In VDBA, you can verify whether journaling is enabled or disabled by clicking Options in the Create Table dialog. This opens the Options dialog, which contains a Journaling check box. If it is checked, journaling is on (enabled); if is not checked, journaling is off (disabled).
By disabling the Journaling check box, you turn off journaling for an individual table, but use caution. For additional information about journaling and the ramifications of disabling journaling at the table level, see the chapter "Backing Up and Recovering Databases."
|
|
|
A table contains duplicate rows when two or more rows are identical.
When you create a table, specify the handling of duplicate rows by clicking Options in the Create Table dialog. This opens the Options dialog, which contains a Duplicates check box. By default, duplicate rows are allowed in any new table that you create, which is indicated by the fact that the Duplicates check box is initially enabled. If you disable this check box, duplicate rows are not allowed in the table. If a user attempts to insert a duplicate row into a table where duplicate rows are not allowed, an error is generated.
Note: Duplicate rows are enforced only when the table has a keyed storage structure. For a description of storage structures, see the chapter "Choosing Storage Structures and Secondary Indexes."
Depending on whether duplicates are allowed, the following tasks are performed differently:
|
|
|
If a table was originally created to allow duplicates, the duplicate rows are preserved, even when the table is modified to another structure.
If a table allows duplicates, duplicate rows can always be inserted.
If a table does not allow duplicates:
|
|
|
If a table allows duplicates, duplicate rows can always be loaded.
If a table does not allow duplicates, duplicate rows:
|
|
|
If a table allows duplicates, rows can always be updated to duplicate other rows.
If a table does not allow duplicates:
If you use the following "bulk increment" update in which the info column has values 1, 2, 3, and so on, every row in the table is updated:
update data set info = info+1;
If duplicates are not allowed, this update fails due to duplicate rows being created.
The new values for the first row are prepared, changing the info column value from 1 to 2. Before inserting the new values, a check is made to see if they violate any constraints. Because the new value, 2, duplicates the value in an existing row, thus violating the criterion stating that duplicates are not allowed, an error is generated and the update is rolled back.
To solve this problem, use either of the following techniques:
|
|
|
In this example, assume the table from which you want to remove the duplicates is named has_dups. This example creates one table based upon the contents of another. For more information, see online help.
Follow these steps to remove duplicate rows:
select distinct * from has_dups
select * from temp
Note: If a table was originally created to allow duplicate rows, but you no longer want the table to allow duplicate rows, you must perform Steps 1-8 above. However, because duplicate row checking is only performed for tables having a keyed structure, you must also perform this additional step:
|
|
|
A page is a block of physical storage space.
When you create a table, specify its page size by clicking Options in the Create Table dialog. This opens the Options dialog, which contains a Page Size drop-down list box. If you need assistance, see online help for details.
The default page size is determined by the DBMS configuration parameter, default_ page_size. The corresponding buffer cache for the installation must also be configured with the page size you specify or you receive an error. For more information, see the chapter "Configuring Ingres" in the System Administrator Guide.
Note: After creating a table, if you later need to add or drop a column, the page size of the table must be larger than 2 KB. If you anticipate that a particular table needs to be altered in either of these ways, create the table using a larger page size or modify its storage structure before attempting to alter the table. For more information, see the chapter "Maintaining Storage Structures."
|
|
|
When you create or alter a table, specify a default value for any column, which is used when no value is specified for the column. Instead of specifying a typical default value of zero or quoted spaces for a column, substitute a particular value as the default value for the new column. To do this, use the associated conversion function for the data type assigned to the new column.
The following table lists the data type and an example of its associated conversion function for creating a column:
Data Type |
Conversion Function |
|---|---|
char(1) |
char(' ') |
c1 |
c(' ') |
varchar(7) |
varchar(' ') |
long varchar |
long_varchar(' ') |
nchar |
nchar(' ') |
nvarchar |
nvarchar (' ') |
text(7) |
text(' ') |
byte(binary) |
byte(0) |
long byte (binary) |
long_byte(0) |
byte varying (binary) |
varbyte(0) |
integer (integer4) |
int4(0) |
smallint (integer2) |
int2(0) |
integer1 |
int1(0) |
float (float8) |
float8(0) |
float4 |
float4(null) |
decimal |
decimal(0) |
ansidate |
ansidate('') or ansidate(null) |
time with time zone |
time_with_tz(' ') or time_with_tz(null) |
time without time zone |
time_wo_tz(' ') or time_wo_tz(null) |
time with local time zone |
time_local(' ') or time_local(null) |
timestamp with time zone |
timestamp_with_tz(' ') or timestamp_with_tz(null) |
timestamp without local time zone |
timestamp_wo_tz(' ') or timestamp_wo_tz(null) |
timestamp with local time zone |
timestamp_local(' ') or timestamp_local(null) |
interval day to second |
interval_dtos(' ') or interval_dtos(null) |
interval year to month |
interval_ytom(' ') or interval_ytom(null) |
ingresdate |
ingresdate(' ') or ingresdate(null) |
money |
money(0) |
object_key |
object_key('01') |
table_key |
table_key('01') |
If the new column is created with no conversion function, the defaults are:
To initialize a column's value to null, specify the default value of null in any of the numeric conversion functions or the date function. Doing so makes the column nullable.
Important! Do not use null as a default value for character fields—this causes an attempt to create a character field of null length, which cannot be done, and returns an error.
|
|
|
When you create or alter a table, define constraints for the table. Constraints are used to check for appropriate data values whenever data is entered or updated in the table.
Constraints are checked at the end of every statement that modifies the table. If the constraint is violated, the DBMS returns an error and aborts the statement. If the statement is in a multi-statement transaction, the transaction is not aborted.
Note: For other mechanisms used to ensure the integrity of data in a table, including integrities and rules, see the chapter "Ensuring Access Security," which also discusses the differences between constraints and integrities.
In VDBA, define constraints using the Create Table or Alter Table dialog.
|
|
|
There are several types of constraints:
|
|
|
You can define unique constraints at both the column and the table level. Columns that you specify as unique or that you use as part of a table-level unique constraint cannot be nullable. Column-level unique constraints ensure that no two rows in the table can have the same value for that column. At the table level, you can specify several columns, all of which are taken together to determine uniqueness.
For example, if you specify the department number and location columns to be unique at the table level, no two departments in the same location can have the same name. On the other hand, specifying the department name and location columns to be unique at the column level is more restrictive—in this case, no two departments can have the same name, regardless of the location, and no two locations can have the same name either.
There is a maximum of 32 columns that you can specify in a table-level unique constraint; however, a table can have any number of unique constraints.
In VDBA, define column-level unique constraints by enabling the Unique check box for the column in either the Create Table or the Alter Table dialog. You define table-level unique constraints using the Table Level Unique Constraint dialog.
|
|
|
Check constraints ensure that the contents of a column fulfills user-specified criteria.
Specify check constraints by entering a Boolean expression involving one or more columns using the Table Level Check Constraint dialog in VDBA.
For example, enter the following expression to ensure that only positive values are accepted in the salary column:
salary > 0
The next example ensures that only positive values are accepted in the budget column and that expenses do not exceed the budget:
budget > 0 and expenses <= budget
|
|
|
Referential constraints are used to validate an entry against the contents of a column in another table (or another column in the same table), allowing you to maintain the referential integrity of your tables. You specify referential constraints by making selections in the Table References dialog in VDBA. For information on referential action options, see the SQL Reference Guide.
When defining a referential constraint, you must consider the following points:
|
|
|
The following example of a referential constraint assumes that the employee table exists with a primary key constraint defined involving a name and an employee number column.
This example verifies the contents of the name and empno columns in the manager table against the primary key columns in the employee table, to ensure that anyone entered into the table of managers is on file as an employee.
To accomplish this, follow these steps:
By default, the Primary Key option is selected and the primary key, which includes comparable name and employee number columns, appears in the edit control at the bottom of the box.
|
|
|
An example of a referential constraint is a Primary key constraint.
Primary key constraints can be used to denote one or more columns, which other tables reference in referential constraints.
Note: Primary key constraints can be used as an alternative and slightly more restrictive form of unique constraint, but need not be used at all.
To define a primary key, you choose which columns are to be part of the key and assign to each a particular position in the key. Columns that are part of the primary key cannot be nullable, and the primary key is implicitly unique. A table can have only one primary key, which can consist of many columns.
In VDBA, you define primary key constraints using the Primary Key button in the Create Table or Alter Table dialog. Clicking this button opens the Primary Key dialog, where you can control which columns are part of the primary key, as well as the order of the columns in the primary key. For details, see online help.
|
|
|
For example, in the partnumbers table, define the partno column as the primary key. Assuming the inventory table had a comparable column named ipartno, define a referential constraint on the inventory table based on the partnumbers table.
To accomplish this, follow these steps:
By default, the Primary Key option is selected and the partno column, which was previously defined as the primary key for the partnumbers table, appears in the edit control at the bottom of the box.
In this case, the part numbers in the inventory table are checked against those in the partnumbers table. When defining this referential constraint, you did not have to specify the column to be referenced in the partnumbers table because it was defined as the primary key.
|
|
|
Special indexes are created whenever you specify a unique, primary key, or referential constraint for a table. No user—including the table owner—can explicitly drop these system-generated constraint indexes, because they are used internally to enforce the constraints.
For primary key and unique constraints, the index is built on the constrained columns as a mechanism to detect duplicates as rows are added to or updated in the table.
For referential constraints, the index is built on the referencing columns of the constraint. This index ensures that the internal procedures that enforce the constraint when a referenced row is deleted or referenced columns are updated can be executed efficiently. When a referencing row is inserted or referencing columns are updated, the unique constraint index built on the referenced columns is used to ensure the efficiency of enforcing the constraint.
Note: If you create an index, and then create a constraint that uses the index, the index cannot be dropped (but the constraint can be dropped). If you create a constraint using the "with index=name" clause but do not create the index (which causes the system to generate the named index), and you drop the constraint, the index is also dropped, because the index is a system index and not a user index.
|
|
|
During table creation, specify options in VDBA for the constraint indexes, with similar options available when you alter a table. For example, the Table Level Unique Constraint dialog (accessible from both the Create Table and the Alter Table dialogs) has an Index button that allows you to fine tune the index used to enforce unique constraints. For additional information about the various dialog options, see online help.
These options give you more control over the index that is created, including the ability to specify:
Constraint indexes are, by default, stored in the default location for data files. Because of storage space or concurrency considerations, they can be stored in an alternate location.
By default, a B-tree storage structure is used for constraint indexes, but in some cases, a different structure can be more efficient. For more information on storage structures, see the chapter "Choosing Storage Structures and Secondary Indexes."
You can save the overhead of generating an unnecessary index if you have an appropriate secondary index available. Simply indicate the name of the secondary index, and it is used to enforce the constraint instead of generating a new one.
To use an existing secondary index for referential constraints, the referencing columns must match the first n columns of the index (although not necessarily in order).
To use an existing secondary index for unique or primary key constraints, the index must be defined on exactly the same columns as the constraint, it must be a unique index, and it must specify that uniqueness is checked only after the update statement is completed.
Note: Indexes enforcing uniqueness constraints in the ANSI/ISO style, as required by referenced columns of a referential constraint, must specify the "unique_scope = statement" option in the corresponding "create index" statement.
For more information on creating a secondary index and specifying the scope for uniqueness checking, see the chapter "Choosing Storage Structures and Secondary Indexes" and online help.
The index built for referential constraints is used only to improve the efficiency of the internal procedures that enforce the constraint when a referenced row is deleted or referenced columns are updated. Because the procedures can execute in its absence, the index is optional.
In the absence of a referential constraint index, the internal procedures use a secondary index, if one is available that is defined on the referencing columns, and so the efficiency of the procedures can not be compromised. However, if no such secondary index exists, a full-table scan of the referencing table is necessary. Thus, choosing not to generate a referential constraint must be reserved for special circumstances, such as when:
This option requires a table that uses a keyed storage structure. Because heap, which is a non-keyed structure, is the default when you create a table, this option cannot be specified for constraints added at that time. The ability to use the base table structure for constraint enforcement is available only for constraints that are added when altering an existing table.
Before you can specify the base table structure in lieu of a constraint index, you need to modify the table to change its storage structure type and to specify key columns to match the constraint columns it is used to enforce. If the base table structure is being used to enforce a primary key or unique constraint, you must also specify that uniqueness is checked only after the update statement is completed.
Note: Indexes enforcing uniqueness constraints in the ANSI/ISO style, as required by referenced columns of a referential constraint, must specify the "unique_scope = statement" option in the corresponding "create index" statement.
For more information on modifying a table's storage structure, specifying key values, and specifying the scope for uniqueness checking, see the chapter "Maintaining Storage Structures."
|
|
|
In VDBA, the Create Table dialog allows you to delete any constraint as you are designing your table, without restrictions. After you have saved the table, remove constraints using the Alter Table dialog; however, removing constraints is more complicated when altering a table, because of the possibility of dependent constraints.
For this reason, each dialog in VDBA that allows you to work with constraints gives you two delete options when altering a table. These same dialogs give you only one delete option when creating a table:
For example, a unique constraint in one table can have a dependent referential constraint in another table. In this case, if you altered the table in which the unique constraint was defined and attempted to perform a Delete operation in the Table Level Unique Constraint dialog, it results in an error due to the existence of the dependent referential constraint. To delete the unique constraint successfully, use Del Cascade, which also deletes the referential constraint in the other table.
Note: In VDBA, column-level unique constraints are defined directly in the Create Table or Alter Table dialog. You cannot, however, remove a column-level unique constraint simply by disabling its Unique check box in the Alter Table dialog. To remove a column-level unique constraint, you must use the Table Level Unique Constraint dialog.
|
|
|
The examples here describe how to change table columns, including:
There are no direct equivalents for changing columns in VDBA or in a single SQL statement.
Note: Renaming a column in a table or changing its data type does not change anything else that is dependent on the column. You need to update any objects, such as reports, forms, and programs, which are dependent on the old column name or data type. In addition, all of the procedures shown here require that you drop the original table, at which point certain other dependent objects, such as integrities, views, indexes, and grants, are also dropped. You must recreate these objects.
Important! We recommend that you back up your tables before changing them. If a problem occurs, you can then restore the original table and repeat the procedure. For additional information, see the chapter "Backing Up and Recovering Databases."
|
|
|
The following example renames two columns, name and addr, to employee and address. The salary column is not renamed. For assistance with any of these steps, see online help.
select name as employee, addr as address, salary
from test
select * from temp
Be sure to update any objects, such as reports, forms, and programs that are dependent on the old column name, and recreate integrities, views, indexes, grants, and other dependent objects that were destroyed when the original table was dropped in Step 4.
|
|
|
When you add a column to an existing table using the Alter Table dialog, the column is placed after the last previously existing column in the table. To insert a new column between existing columns, you must follow a procedure similar to that outlined for renaming a column.
The following example illustrates inserting a new column, newcol, in the middle of an existing table with previously defined columns. If you need assistance with any of these steps, see online help.
select col1, col2, varchar(' ') as newcol,
col3, col4 from test
select * from temp
Be sure to recreate integrities, views, indexes, grants, and other dependent objects that were destroyed when the original table was dropped in Step 4.
Note: To rearrange the current column order of a table without adding new columns, use this same procedure, selecting the columns in the desired order in Step 3.
|
|
|
As a database grows, it can become necessary to move some of its tables to an alternate location. If a table has grown so large that you can no longer work with it at the current location, or the table needs to be distributed across multiple disks to improve performance, modify the table to use an alternate location.
Note: Before modifying the table, make sure you have met the requirements, as described in Requirements for Using an Alternate Location for a Table.
You can modify a table's location using one of the following techniques:
In VBDA, use the Modify Table Structure dialog. For a detailed procedure, see online help. For other uses of this dialog, see the chapter "Maintaining Storage Structures."
|
|
|
Using the Modify Table Structure dialog in VDBA, you can move the data files for a table from one location to another. This is accomplished using the Locations button available when the Relocate radio button is enabled, which opens the Relocate Table dialog. For information on using this dialog, see online help.
Using this operation, it is possible to change one or more of the locations currently used by the table, without changing the number of locations used. For example:
|
|
|
You can increase or decrease the number of locations currently used by a table for its data files.
To do this, use the modify to reorganize SQL statement. In VDBA, use the Modify Table Structure dialog. Use the Locations button, which is available when the Change Locations radio button is enabled, which opens the Change Locations dialog. For specific information on using this dialog, see online help.
This operation requires more overhead than simply relocating a table because it performs a table reorganization in addition to moving files. Using this operation, you can:
Afterwards, the table is reorganized to spread equally, in sized blocks, over the specified locations.
|
|
|
By default, when you create a table, it has no expiration date. To give a table an expiration date, use the save statement as described in the SQL Reference Guide.
A table is not automatically destroyed on its expiration date.
|
|
|
To purge expired tables, select Expired_Purge as the Operation in the Verify Database dialog in VDBA. For details on using this dialog, see online help.
Note: The Verify Database dialog has many other uses. For specific information on using this dialog, see online help.