Using Caché SQL
Defining Tables
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

This chapter describes how you create tables in Caché SQL. It discusses the following topics:

Table Names and Schema Names
You can create a table either by defining the table (using CREATE TABLE) or by defining a persistent class that is projected to a table:
The correspondence between these two names may not be identical for the following reasons:
System-wide Default Schema
A table name is either qualified (schema.tablename) or unqualified (tablename).
If you create a table or other item with an unqualified name, Caché assigns it the system-wide default schema name, and the corresponding persistent class package name. If a named or default schema does not exist, Caché creates the schema (and package) and assigns the created item to the schema. If you delete the last item in a schema, Caché deletes the schema (and package). The following description of schema name resolution applies to table names, view names, and stored procedure names.
The initial system-wide default schema name is SQLUser. The corresponding persistent class package name is User. Therefore, either the unqualified table name Employee or the qualified table name SQLUser.Employee would generate the class User.Employee. For this reason, attempting to specify a schema name of User results in an SQLCODE error.
To return the current system-wide default schema name, invoke the $SYSTEM.SQL.DefaultSchema() method:
  WRITE $SYSTEM.SQL.DefaultSchema()
 
You can change the schema default using either of the following:
Caution:
When you change the default SQL schema name, the system automatically purges all cached queries in all namespaces on the system. By changing the default schema name, you change the meaning of all queries that contain unqualified table, view, or stored procedure names. It is strongly recommended that the default SQL schema name be established at Caché installation and not subsequently modified.
The schema name is used to generate the corresponding class package name. Because these names have different naming conventions, they may not be identical.
_CURRENT_USER Keyword
Schema Search Path
When accessing an existing table (or view, or stored procedure) for a DML operation, an unqualified name is supplied the schema name from the schema search path. Schemas are searched in the order specified and the first match is returned. If no match is found in the schemas specified in the search path, or no search path exists, the system-wide default schema is used. (Note that the #Import macro directive uses a different search strategy and does not “fall through” to the system-wide default schema.)
If the unqualified name does not match any of the schemas specified in the schema search path or the system-wide schema default, an SQLCODE -30 error is issued, such as the following: SQLCODE: -30 Message: Table 'PEOPLE' not found within schemas: CUSTOMERS,EMPLOYEES,SQLUSER.
Schema Naming Considerations
Schema names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters. A schema name cannot be specified as a delimited identifier. Attempting to specify “USER” or any other SQL reserved word as a schema name results in an SQLCODE -312 error. The INFORMATION_SCHEMA schema name and the corresponding INFORMATION.SCHEMA package name are reserved in all namespaces. Users should not create tables/classes within this schema/package.
When you issue a create operation, such as CREATE TABLE, that specifies a schema that does not yet exist, Caché creates the new schema. Caché uses the schema name to generate a corresponding package name. Because the naming conventions for schemas and their corresponding packages differ, the user should be aware of name conversion considerations for non-alphanumeric characters. These name conversion considerations are not the same as for tables:
Platform-Specific Schema Names
When creating an ODBC-based query to run from Microsoft Excel via Microsoft Query on the Mac, if you choose a table from the list of those available, the generated query does not include the table’s schema (equivalent to the package for a class). For example, if you choose to return all the rows of the Person table from the Sample schema (in the Samples namespace), the generated query is:
SELECT * FROM Person
Because Caché interprets an unqualified table name as being in the SQLUser schema, this statement either fails or returns data from the wrong table. To correct this, edit the query (on the SQL View tab) to explicitly refer to the desired schema. The query should then be:
SELECT * FROM Sample.Person
Table Naming Considerations
Table names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters.
When you use the CREATE TABLE command to create a table, Caché uses the table name to generate a corresponding class name. Because the naming conventions for tables and their corresponding classes differ, the user should be aware of name conversion considerations for non-alphanumeric characters:
Attempting to specify “USER” or any other SQL reserved word as a table name or schema name results in an SQLCODE -312 error. To specify an SQL reserved word as a table name or schema name, you can specify the name as a delimited identifier. If you use a delimited identifier to specify a table or schema name that contains non-alphanumeric characters, Caché strips out these non-alphanumeric characters when generating the corresponding class or package name.
The following table name length limits apply:
For further details on table names, refer to the CREATE TABLE command in the Caché SQL Reference. For further details, on classes refer to Caché Classes in the Using Caché Objects manual.
RowID Field
The RowID field is a system-generated field that uniquely identifies each record with a sequential integer. When you define a table, Caché SQL automatically defines this field. When a table is populated with data, Caché assigns sequential integers to this field, starting with 1. RowID data values are defined as required, unique, non-null, and non-modifible.
By default, Caché names this field “ID”. However this field name is not reserved. If the user defines a field named “ID”, Cache names the RowId as “ID1”. If, for example, the user then uses ALTER TABLE to define a field named “ID1”, Caché renames the RowID as “ID2”, and so forth. For this reason, Caché provides the %ID pseudo-column name (alias) which always returns the RowID (object ID) value, regardless of the name assigned to the RowID.
By default, Caché defines this field as column number 1.
ALTER TABLE cannot modify or delete the RowID field definition.
Row ID values always increment. They are not reused. Therefore, if the table data has been modified by delete and insert operations, the row ID values will be in ascending numeric sequence (the order of insert), but will not be numerically contiguous. To determine if a specific ID value exists, invoke the table’s %ExistsId() method.
The RowID counter is reset to 1 by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table.
By default, the RowID is hidden (not displayed by SELECT *) and PRIVATE. When you create a table you can specify the %PUBLICROWID keyword to make the RowId not hidden and public. Because this keyword specifies that the table’s RowID is PUBLIC, the RowID can therefore be used as a foreign key reference. If you specify the %PUBLICROWID keyword, the class corresponding to the table is defined with “Not SqlRowIdPrivate”. This optional keyword can be specified anywhere in the CREATE TABLE comma-separated list of table elements. It cannot be specified in ALTER TABLE.
Note:
Most of the example tables supplied in the Samples namespace are defined with %PUBLICROWID.
To list the field names (hidden and non-hidden) in a table , refer to Column Names and Numbers.
By default, RowID values are not user-modifiable. Modifying RowId values can have serious consequences and should only be done in very specific cases and with extreme caution. The Config.SQL.AllowRowIDUpdate property allows RowID values to be user-modifiable.
For further details, refer to The RowID Field and %PUBLICROWID in the CREATE TABLE reference page.
RowVersion and Serial Counter Fields
InterSystems SQL supports two special-purpose data types for automatically-incrementing counter values:
RowVersion Field
The RowVersion field is an optional user-defined field that provides row-level version control, allowing you to determine the order in which changes were made to the data in each row namespace-wide. Caché maintains a namespace-wide counter, and assigns a unique incremental positive integer to this field each time the row data is modified (insert, update, or %Save). Because this counter is namespace-wide, an operation on one table with a ROWVERSION field sets the increment point for the ROWVERSION counter that is used for all other tables with a ROWVERSION field in the same namespace.
You create a RowVersion field by specifying a field of data type ROWVERSION. You can only specify one ROWVERSION data type field per table. Attempting to create a table with more than one ROWVERSION field results in a 5320 compilation error.
This field can have any name and can appear in any column position. The ROWVERSION (%Library.RowVersion) data type maps to BIGINT (%Library.BigInt).
This field receives a positive integer from an automatically incremented counter, starting with 1. This counter increments whenever data in any ROWVERSION-enabled table is modified by an insert, update, or %Save operation. The incremented value is recorded in the ROWVERSION field of the row that has been inserted or updated.
A namespace can contain tables with a RowVersion field and tables without this field. Only data changes to tables that have a RowVersion field increment the namespace-wide counter.
When a table is populated with data, Caché assigns sequential integers to this field for each inserted row. If you use ALTER TABLE to add a ROWVERSION field to a table that already contains data, this field is created as NULL for pre-existing fields. Any subsequent insert or update to the table assigns a sequential integer to the RowVersion field for that row. This field is read-only; attempting to modify a RowVersion value generates an SQLCODE -138 error: Cannot INSERT/UPDATE a value for a read only field. Therefore, a RowVersion field is defined as unique and non-modifible, but not required or non-null.
RowVersion values always increment. They are not reused. Therefore, inserts and updates assign unique RowVersion values in temporal sequence. Delete operations remove numbers from this sequence. Therefore, RowVersion values may not be numerically contiguous.
This counter is never reset. Deleting all table data does not reset the RowVersion counter. Even dropping all tables in the namespace that contain a ROWVERSION field does not reset this counter.
The RowVersion field should not be included in a unique key or primary key. The RowVersion field cannot be part of an IDKey index.
The RowVersion field is not hidden (it is displayed by SELECT *).
This is shown in the following example of three tables in the same namespace.
  1. Create Table1 and Table3, each of which has a ROWVERSION field, and Table2 that does not have a ROWVERSION field.
  2. Insert ten rows into Table1. The ROWVERSION values of these rows are the next ten counter increments. Since the counter has not previously been used, they are 1 through 10.
  3. Insert ten rows into Table2. Because Table2 does not have a ROWVERSION field, the counter is not incremented.
  4. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (11 in this case).
  5. Insert ten rows into Table3. The ROWVERSION values of these rows are the next ten counter increments (12 through 21).
  6. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (22 in this case).
  7. Delete a row of Table1. The ROWVERSION counter is unchanged.
  8. Update a row of Table3. The ROWVERSION values for this row is changed to the next counter increment (23 in this case).
Serial Field
You can use the SERIAL data type (%Library.Counter in a persistent class table definition) to specify one or more optional integer counter fields to record the order of inserts of records into a table. By default, this field receives a positive integer from an automatically incremented table counter whenever a row is inserted into the table. However, a user can specify an integer value for this field during an insert, overriding the table counter default.
Attempting to UPDATE a counter field value results in an SQLCODE -105 error.
This counter is reset to 1 by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table.
Defining a Table by Creating a Persistent Class
The primary way to define tables within Caché is to use Studio to create persistent class definitions. When these classes are saved and compiled within the Caché database, they automatically projects to a relational table that corresponds to the class definition: each class represents a table; each property represents a column, and so on. The maximum number of properties (columns) definable for a class (table) is 1000.
For example, the following defines the persistent class MyApp.Person:
Class MyApp.Person Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
}
When compiled, this creates the MyApp.Person persistent class and the corresponding SQL table, Person within the MyApp schema. For details on how to perform these operations, refer to Defining and Compiling Classes in the Defining and Using Classes manual.
In this example, the package name MyApp is specified. When defining a persistent class, an unspecified package name defaults to User; this corresponds to the default SQL schema name SQLUser. For example, defining a table named Students as a persistent class creates the class User.Students, and the corresponding SQL schema.table name SQLUser.Students.
In this example, the persistent class name Person is the default SQL table name. You can use the SqlTableName class keyword to supply a different SQL table name.
The same MyApp.Person table could have been defined using the DDL CREATE TABLE statement, specifying the SQL schema.table name. Successful execution of this SQL statement generates a corresponding persistent class with package name MyApp and class name Person:
CREATE TABLE MyApp.Person (
    Name VARCHAR(50) NOT NULL,
    SSN VARCHAR(15) DEFAULT 'Unknown',
    DateOfBirth DATE,
    Sex VARCHAR(1)
)
CREATE TABLE does not specify an explicit StorageStrategy in the corresponding class definition. It instead takes the defined default storage strategy.
By default, CREATE TABLE specifies the Final class keyword in the corresponding class definition, indicating that it cannot have subclasses.
For an introduction to how the object view of the database corresponds to the relational view, see Introduction to the Default SQL Projection in the chapter “Introduction to Persistent Objects” of Using Caché Objects.
Note that a persistent class definition such as the one shown above creates the corresponding table when it is compiled, but this table definition cannot be modified or deleted using SQL DDL commands (or by using the Management Portal Drop action), which give you the message “DDL not enabled for class 'schema.name'...”). You must specify [DdlAllowed] in the table class definition to permit these operations:
Class MyApp.Person Extends %Persistent [DdlAllowed]
You can specify %Populate in the class definition to enable automatic populating of the table with test data.
Class MyApp.Person Extends (%Persistent,%Populate) [DdlAllowed]
This provides a Populate() method for the class. Running this method populates the table with ten rows of test data.
Unique Values
CREATE TABLE allows you to define a field as UNIQUE. This means that every field value is a unique (non-duplicate) value.
Defining at table as a persistent class does not support a corresponding property keyword. Instead, you must define both the property and a unique index on that property. The following example provides for a unique Num value for each record.
  Class Sample.CaveDwellers Extends %Persistent [ DdlAllowed ]
  { 
  Property Num As %Integer;
  Property Troglodyte As %String(MAXLEN=50);
  Index UniqueNumIdx On Num [ Type=index,Unique ];
  }
Having a unique value field is necessary for using the INSERT OR UPDATE statement.
For reference material on class property keywords, refer to the Property Keywords chapter of Caché Class Definition Reference.
Computed Values
The following class definition example defines a table that includes a field (Birthday) that uses SqlComputed to compute its value when you initially set the DateOfBirth field value and SqlComputeOnChange to recompute its value when you update the DateOfBirth field value. The Birthday field value includes the current timestamp to record when this field value was computed/recomputed:
Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
  Property Name As %String(MAXLEN=50) [Required];
  Property DateOfBirth As %Date;
  Property Birthday As %String 
          [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DateOfBirth},9),",")_
                              " changed: "_$ZTIMESTAMP},
                              SqlComputed, SqlComputeOnChange = DateOfBirth ];
}
Note that an UPDATE to DateOfBirth that specifies the existing DateOfBirth value does not recompute the Birthday field value. For the corresponding SQL code, refer to the COMPUTECODE section of the CREATE TABLE reference page.
For reference material on class property keywords, refer to the Property Keywords chapter of Caché Class Definition Reference.
Class Methods
You can specify class methods as part of a table definition, as shown in the following example:
Class MyApp.Person Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
  {
   QUIT 123
  }
}
In a SELECT query you can invoke this method as follows:
SELECT Name,SSN,MyApp.Numbers() FROM MyApp.Person
Defining a Table by Using DDL
You can define tables in Caché SQL using standard DDL commands:
Available DDL Commands in Caché SQL
ALTER Commands CREATE Commands DROP Commands
These are described in the Caché SQL Reference.
You can execute DDL commands in a variety of ways, including:
Using DDL in Embedded SQL
Within an ObjectScript method or routine, you can use embedded SQL to invoke DDL commands.
For example, the following method creates a TEST.EMPLOYEE table:
ClassMethod CreateTable() As %Integer
{
 &sql(CREATE TABLE TEST.EMPLOYEE (
    EMPNUM              INT NOT NULL,
    NAMELAST            CHAR (30) NOT NULL,
    NAMEFIRST           CHAR (30) NOT NULL,
    STARTDATE           TIMESTAMP,
    SALARY              MONEY,
    ACCRUEDVACATION     INT,
    ACCRUEDSICKLEAVE    INT,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM)))
        
 Write "SQL: ",SQLCODE,!
 QUIT SQLCODE
}
When this method is invoked it attempts to create a TEST.EMPLOYEE table (as well as the corresponding TEST.EMPLOYEE class). If successful, the SQLCODE variable is set to 0. If unsuccessful, SQLCODE contains an SQL Error Code indicating the reason for the failure.
The most common reasons that a DDL command such as this one will fail are:
Using a Class Method to Execute DDL
Within ObjectScript or Caché Basic, you can use the Dynamic SQL %SQL.Statement object to prepare and execute DDL commands using Dynamic SQL.
The following example defines a class method to create a table using Dynamic SQL:
  Class Sample.NewT
  {
  ClassMethod DefTable(user As %String,pwd As %String) As %Status [Language=cache]
    {
    DO ##class(%SYSTEM.Security).Login(user,pwd)
    SET myddl=2
    SET myddl(1)="CREATE TABLE Sample.MyTest "
    SET myddl(2)="(NAME VARCHAR(30) NOT NULL,SSN VARCHAR(15) NOT NULL)"
    SET tStatement=##class(%SQL.Statement).%New()
    SET tStatus=tStatement.%Prepare(.myddl)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset=tStatement.%Execute()
    IF rset.%SQLCODE=0 {WRITE "Created a table"}
    ELSEIF rset.%SQLCODE=-201 {WRITE "table already exists"}
    ELSE {WRITE "Unexpected error SQLCODE=",rset.%SQLCODE}
    }
  }
This method is invoked as follows:
  DO ##class(Sample.NewT).DefTable("myname","mycachepassword")
As with the embedded SQL example, this method will fail if there is no current user logged in.
Running DDL Scripts from the Command Line
You can import Caché SQL DDL script files using either the Cache() method interactively from a Terminal session, or the DDLImport("CACHE") method as a background job. For further details, refer to the Importing SQL Code chapter of this guide.
If you are migrating tables from a relational database to Caché, you may have one or more DDL scripts within text files. Caché provides methods to help load such tables into Caché. Refer to the %SYSTEM.SQL class for details (particularly the Oracle(), Sybase(), and MSSQLServer() methods).
For example, to load an Oracle DDL file from the Caché command line:
  1. Start a Terminal session using the Terminal command in the “Caché Cube” menu.
  2. Switch to the namespace in which you wish to load the table definitions:
     ZN "MYNAMESPACE"
  3. Invoke the desired DDL import method:
     DO $SYSTEM.SQL.Oracle()
    and follow the directions displayed at the terminal.
Defining a Table by Querying an Existing Table
You can use the $SYSTEM.SQL.QueryToTable() method to define and populate a new table based on an existing table. You specify a query and a new table name. The existing table name and/or the new table name can be qualified or unqualified. The query can contain JOIN syntax. The query can supply column name aliases that become the column names in the new table.
  1. QueryToTable() copies the DDL definition of a existing table and assigns it the specified new table name. It copies the definitions of the fields specified in the query, including the data type, maxlength, and minval/maxval. It does not copy field data constraints, such as default value, required value, or unique value.
    If the query specifies SELECT * or SELECT %ID, the RowID field of the original table is copied as a non-required, non-unique data field of data type integer. QueryToTable() generates a unique RowID field for the new table. If the copied RowID is named ID, the generated RowID is named ID1.
    QueryToTable() creates a corresponding persistent class for this new table. The persistent class is defined as DdlAllowed. The owner of the new table is the current user.
    The new table is defined with %Cache storage = YES and Supports Bitmap Indices = YES, regardless of these settings in the source table.
    The only index created for the new table is the IDKEY index. No bitmap extent index is generated. Index definitions for the copied fields are not copied into the new table.
    References from a field to another table are not copied.
  2. QueryToTable() then populates the new table with data from the fields selected by the query. It sets the table’s Extent Size to 100,000. It estimates the IDKEY Block Count. Run Tune Table to set the actual Extent Size and Block Count, and the Selectivity and Average Field Size values for each field.
QueryToTable() both creates a table definition and populates the new table with data. If you wish to only create a table definition, specify a condition in the query WHERE clause that selects for no data rows. For example, WHERE Age < 20 AND Age > 20.
The following example copies the Name, and Age, fields from Sample.Person and creates an AVG(Age) field. These field definitions are used to create a new table named Sample.Youth. The method then Populates Sample.Youth with the Sample.Person data for those records where Age < 21. The AvgInit field contains the aggregate value for the selected records at the time that the table was created.
  DO $SYSTEM.SQL.QueryToTable("SELECT Name,Age,AVG(Age) AS AvgInit FROM Sample.Person WHERE Age < 21","Sample.Youth",1,.errors)
External Tables
In Caché SQL, you can also have “external tables,” tables that are defined within the Caché dictionary but are stored within an external relational database. External tables act as if they were native Caché tables: you can issue queries against them and perform INSERT, UPDATE, and DELETE operations. The access to external database is provided by the Caché SQL Gateway, which offers transparent connectivity using ODBC or JDBC. See Using the Caché SQL Gateway for more details.
Listing Tables
The INFORMATION.SCHEMA.TABLES persistent class displays information about all tables (and views) in the current namespace. It provides a number of properties including the schema and table names, the owner of the table, and whether you can insert new records.
The following example returns the table type, schema name, table name, and owner for all tables and views in the current namespace:
SELECT Table_Type,Table_Schema,Table_Name,Owner FROM INFORMATION_SCHEMA.TABLES
You can display much of the same information as INFORMATION.SCHEMA.TABLES for a single table using the Catalog Details tab in the Management Portal SQL Interface.
Listing Column Names and Numbers
You can list all of the column names (field names) for a specified table in three ways:
The GetColumns() Method
To list the names of the columns in a table in column number order, you can use the GetColumns() method, as follows:
  SET stat=##class(%SYSTEM.SQL).GetColumns("Sample.Person",.byname,.bynum)
  IF stat=1 {
    SET i=1
    WHILE $DATA(bynum(i)) { WRITE "name is ",bynum(i),"   col num is ",i,!
                            SET i=i+1 }
  }
  ELSE { WRITE "GetColumns() cannot locate specified table" }
 
GetColumns() lists all defined columns, including hidden columns.
You can also use this method to determine the column number for a specified column name, as follows:
  SET stat=##class(%SYSTEM.SQL).GetColumns("Sample.Person",.byname)
  IF stat=1 {
         WRITE "Home_State is column number ",byname("Home_State"),!  }
  ELSE { WRITE "GetColumns() cannot locate specified table" }