ALLBASE/SQL Reference Manual
> Chapter 2 Using ALLBASE/SQLDefining How Data is Stored and Retrieved |
||||||||||||||||||||||||
|
Creating a TableWhen you define a table, use the CREATE TABLE statement to accomplish the following tasks:
1 ---2--- | | | CREATE PUBLIC TABLE PurchDB.Parts (PartNumber CHAR(16) NOT NULL, --- PartName VARCHAR(30), | --3 SalesPrice DECIMAL (10,2)) --- IN WarehFS | 4You can also specify native language characteristics and integrity constraints at both the table and the column level. Choosing the Locking Mode and Default Access AuthoritiesALLBASE/SQL uses one of four locking modes for controlling access to data in a table by different transactions. A transaction is one or more SQL statements that together perform a unit of work. The locking modes are as follows:
Naming the Table and ColumnsThe name you assign to a table or column can be up to 20 bytes long and is governed by the rules in Chapter 6 "Names".Defining the ColumnsYou enclose the column definitions in parentheses, separating multiple column definitions with a comma. At least one column must be defined. Each column is defined by a name and a data type.Specifying Data TypesData types describe the kind of data that can be stored in a column. ALLBASE/SQL has five numeric data types, two string data types, four date/time data types, and four binary data types as follows:
Specifying Column OptionsYou can also specify a NOT NULL, DEFAULT, native language, or constraints option for each column. The native language and constraint options are discussed in separate sections below. When you define a column as NOT NULL, ALLBASE/SQL ensures that it contains no null values. NULL is a special data type that indicates the absence of a value. The DEFAULT option allows you to specify a default value for a column. If the DEFAULT option is defined for a column and a value is not specified when an INSERT statement is executed, ALLBASE/SQL inserts the default value. Default values are of the following types:
CREATE TABLE PurchDB.Parts (Column 1 char(20), Column 2 DEFAULT NULL)You cannot use the DEFAULT option for a LONG data type column. Specifying a DBEFileSetThe table rows are stored in the DBEFiles previously associated with the DBEFileSet named in the IN clause of the CREATE TABLE statement. If you do not specify a DBEFileSet, rows for the table are stored in the SYSTEM DBEFileSet. For best performance, explicitly specify a DBEFileSet other than the SYSTEM DBEFileSet. Specifying Native Language Tables and ColumnsUse the LANG = TableLanguageName option in the CREATE TABLE statement to specify a language other than the DBEnvironment's language. You can only specify NATIVE 3000 or the current native language of the DBEnvironment. CREATE TABLE NewTable LANG = "NATIVE 3000" (Column1 char(20), Column2 char(10))You must use double quotes around the name "NATIVE 3000" because it contains a hyphen. Normally, native language names do not require quotes. For more information on naming rules, refer to the "Names" chapter. Use the LANG = ColumnLanguageName option in the column definitions of the CREATE TABLE statement to specify a column with a language different from that of the table as a whole. For example: CREATE TABLE NewTable (Column1 char(20) LANG = "NATIVE 3000", Column2 char(20))Sorting and pattern matching follow the rules of the column language. In order to maintain ASCII performance as much as possible, NATIVE 3000 column sorting and matching are done in ASCII. By default, the language of a new table is the language of the DBEnvironment, and the language of a new column is the language of the table it belongs to. Creating a ViewA view is a table derived by placing a "window" over one or more tables to let users or programs see only certain data. Views are useful for limiting data visibility; they are also useful for pulling together data from various tables for easier use. The tables from which data for the view is derived are called base tables. You define a view with the CREATE VIEW statement. The following are components of a view definition:
1 | CREATE VIEW HiPrice (PartNum, Price) --2 AS SELECT PartNumber, SalesPrice --- FROM PurchDB.Parts |--3 WHERE SalesPrice > 1000 ---View names are governed by the same rules as table names. The columns in a view can have the same names as the columns in the table(s) they are based on, or they can have different names. You only need to include column names in a view definition if you are using multiple base tables which have duplicate column names or if you want to rename the columns. You enclose the names in parentheses, but omit data types, which depend on the types of the columns in the base tables. The derivation of the view is a SELECT statement. In the previous example, the view is derived from the PurchDB.Parts table. Each row in the view contains a part number and a price; only rows for parts costing more than $1000 can be accessed through this view. Unlike a table definition, a view definition does not require that you specify where to store rows. A view is a SELECT statement stored in the system catalog, not a physical copy of the data; ALLBASE/SQL extracts data from physical tables at the time you use the view. Views can be used for both retrieving and modifying data. Refer to "Updatability of Queries" in Chapter 3 "SQL Queries" for restrictions governing the use of a view to change data in a base table. The WITH CHECK OPTION for views is described in Chapter 4 "Constraints, Procedures, and Rules". Creating IndexesYou can create an index on one or more columns in a query. An index can provide quick access to the data in your tables. For information on indexes, refer to section "Understanding Data Access Paths" later in this section. Specifying Integrity ConstraintsUsing integrity constraints helps to ensure that a database contains only valid data. Integrity constraints provide a way to check data within the database system rather than by coding elaborate validation checks within application programs. An integrity constraint is either a unique constraint, a referential constraint, or a check constraint. These constraints are described in Chapter 4 "Constraints, Procedures, and Rules". Creating ProceduresYou can define procedures to enforce relationships among database tables or to automate nearly any operation in the DBEnvironment. The following example shows creating a procedure to perform deletions from the SupplyPrice table in the sample DBEnvironment PartsDBE: CREATE PROCEDURE PurchDB.DelSupply(Part CHAR(16) NOT NULL) AS BEGIN DELETE FROM PurchDB.SupplyPrice WHERE PartNumber = :Part; ENDThe procedure definition includes a parameter declaration. The parameter Part accepts a value into the procedure at run time. You execute the procedure with a statement like the following example: EXECUTE PROCEDURE PurchDB.DelSupply ('1123-P-01')The effect of the procedure is to delete all rows in the SupplyPrice table whose part number is 1123-P-01. For detailed information about creating and using procedures, refer to Chapter 4 "Constraints, Procedures, and Rules". Creating RulesOnce a table is defined, you can create a rule that will execute a procedure whenever a specific firing condition is met. For example, you can define a rule that will execute a procedure to delete rows from the SupplyPrice table whenever a specific part is dropped from the Parts table in the sample DBEnvironment PartsDBE: CREATE RULE PurchDB.RemovePart AFTER DELETE FROM PurchDB.Parts EXECUTE PROCEDURE PurchDB.DelSupply (PartNumber)Once the rule exists, you activate it by performing a DELETE: DELETE FROM PurchDB.Parts WHERE PartNumber = '1123-P-01'For detailed information about creating and using rules, refer to the "Constraints, Procedures, and Rules" chapter.
|