SQL Naming and Statement Rules

This section briefly describes the SQL naming and statement rules, as well as the additional features and extensions of SQL and the database management system (DBMS).

The following statements and features enable the control of:

• Access to information in the database - Enhancements to the Grant SQL statement allow you to specify which users can view, add, change, or delete data from a table. In addition, table access for groups of users and for individual applications can be controlled.
• Access to computing resources - The Grant SQL statement allows control of user consumption of computing resources. For example, the amount of I/O a user can perform can be limited and the approximate maximum number of rows that can be returned by a query issued by the user to prevent a runaway query.
• Referential integrity - There are two mechanisms for ensuring that the data in various tables maintain the relationships required for your business purposes: rules and referential constraints. Both allow relationships between specific columns of different tables to be specified, and to specify actions to be performed when a change to a table violates the relationships you require.

Object Naming Rules

The rules for naming database objects (such as tables, columns, views, and database procedures) are as follows:

• Names can contain only alphanumeric characters and must begin with an alphabetic character or an underscore (_). Database names must begin with an alphabetic character, and cannot begin with an underscore.
• Case significance (upper or lower) is determined by the settings for the database in which the object is created (Ingres or ANSI/ISO Entry SQL-92-compliant) and differs for delimited and non-delimited identifiers.

  For details about delimited identifiers, see Regular and Delimited Identifiers in this chapter.

• Names can contain (though cannot begin with) the following special characters: 0 through 9, #, @, and $. Names specified as delimited identifiers (in double quotes) can contain additional special characters.

  For details about delimited identifiers, see Regular and Delimited Identifiers in this chapter.

• Database objects (such as tables, columns, views, and database procedures) cannot begin with the letters, ii. This name is reserved for use by the DBMS Server.
• The maximum length of an object name is 32 characters. Database names must be unique to 24 characters (or the maximum file name length imposed by your operating system, if less than 24).

  The following are examples of objects managed by Ingres tools (such as VIFRED or Vision):

  • Forms
  • JoinDefs
  • QBFNames
  • Graphs
  • Reports
• Avoid assigning reserved words as object names. A list of reserved words can be found in the appendix "Keywords."

Regular and Delimited Identifiers

Identifiers in SQL statements specify names for the following objects:

• Authorization identifier (user, group, or role)
• Column
• Constraint
• Correlation name
• Cursor
• Database event
• Database procedure
• Database procedure label
• Database procedure parameter
• Database procedure variable
• Index
• Location
• Prepared query
• Rule
• Savepoint
• Schema
• Synonym
• Table
• View

Specify these names using regular (unquoted) identifiers or delimited (double-quoted) identifiers. For example:

• Table name in a Select SQL statement specified using a regular identifier:

  select * from employees

• Table name in a Select SQL statement specified using a delimited identifier:

  select * from "my table"

Delimited identifiers enable you to embed special characters in object names. The use of special characters in regular identifiers is restricted.

Note: Case sensitivity for delimited identifiers are specified when a database is created. For compliance with ANSI/ISO Entry SQL-92, delimited identifiers must be case sensitive.

Restrictions on Identifiers

The following table lists the restrictions for regular and delimited identifiers (the names assigned to database objects):

Note: The maximum length of an identifier is 32 characters. For ANSI/ISO Entry SQL-92 compliance, identifiers must be no longer than 18 characters.

The following characters cannot be embedded in object names using either regular or delimited identifiers:

DEL (ASCII 127 or X'7F')

To specify double quotes in a delimited identifier, repeat the quotes.

For example:

"""Identifier""Name"""

is interpreted as:

"Identifier"Name"

Trailing spaces are deleted from object names specified using delimited identifiers.

For example:

create table "space test " (scolumn int);

creates a table named, space test, with no trailing blanks (leading blanks are retained).

If an object name composed entirely of spaces is specified, the object is assigned a name consisting of a single blank. For example, the following creates a table named " ".

create table "     " (scolumn int);

Case Sensitivity of Identifiers

Case sensitivity for regular and delimited identifiers is specified at the time a database is created. By default, delimited identifiers are not case sensitive. For compliance with ANSI/ISO Entry SQL-92, however, delimited identifiers must be case sensitive.

The DBMS Server treats database, user, group, role, cursor, and location names without regard to case, and mixed-case database or location names cannot be created.

Comment Delimiters

To indicate comments in interactive SQL, use the following delimiters:

/* and */ (left and right delimiters, respectively).

For example:

/* This is a comment */

When using /*...*/ to delimit a comment, the comment can continue over more than one line. For example:

/* Everything from here...
...to here is a comment */

The delimiter, --, indicates that the rest of the line is a comment. A comment delimited by -- cannot be continued to another line.-- (left side only).

For example:

--This is a comment.

To indicate comments in embedded SQL, use the following delimiters:

--, with the same usage rules as interactive SQL. Host language comment delimiters.

For information about comment delimiters, see the Embedded SQL Companion Guide.

Statement Terminators

Statement terminators separate one SQL statement from another. In interactive SQL, the statement terminator is the semicolon (;). Terminate statements with a semicolon when entering two or more SQL statements before issuing the go command (\g), selecting the Go menu item, or issuing some other terminal monitor command.

In the following example, semicolons terminate the first and second statements. The third statement does not need to be terminated with a semicolon, because it is the final statement.

select * from addrlst;
select * from emp
     where fname = 'john';
select * from emp
     where mgrname = 'dempsey'\g

If only one statement is entered, the statement terminator is not required. For example, the following single statement does not require a semicolon:

select * from addrlst\g

In embedded SQL applications, the use of a statement terminator is determined by the rules of the host language. For details, see the Embedded SQL Companion Guide.

Correlation Names

Correlation names are used in queries to clarify the table (or view) to which a column belongs or to abbreviate long table names. For example, the following query uses correlation names to join a table with itself:

select a.empname from emp a, emp b
     where a.mgrname = b.empname
     and a.salary > b.salary;

Correlation Names Rules

Correlation names can be specified in a Select, Delete, Update, Create Integrity, or Create Rule SQL statement. The rules of using correlation names are as follows:

• A single query can reference a maximum of 126 table names (including all base tables referenced by views specified in the query).
• If a correlation name is not specified, the table name implicitly becomes the correlation name. For example, in the following query:

  delete from employee
       where salary > 100000;

  the DBMS Server assumes the correlation name of employee for the salary column and interprets the preceding query as:

  delete from employee
       where employee.salary > 100000;

• If a correlation name for a table is specified, use the correlation name (and not the actual table name) within the query. For example, the following query generates a syntax error:

  /*wrong*/
  delete from employee e
       where employee.salary > 35000;

• A correlation name must be unique. For example, the following statement is illegal because the same correlation name is specified for different tables:

  /*wrong*/
  select e.ename from employee e, manager e
       where e.dept = e.dept;

• A correlation name that is the same as a table that you own, cannot be specified. If you own a table called mytable, the following query is illegal:

  select * from othertable mytable...;

  In nested queries, the DBMS Server resolves unqualified column names by checking the tables specified in the nearest from clause, then the from clause at the next highest level, and so on, until all table references are resolved.

  For example, in the following query, the dno column belongs to the deptsal table, and the dept column to the employee table.

  select ename from employee
       where salary >
       (select avg(salary) from deptsal
        where dno = dept);

  Because the columns are specified without correlation names, the DBMS Server performs the following steps to determine to which table the columns belong:

  • dno

    The DBMS Server checks the table specified in the nearest from clause (the deptsal table). The dno column does belong to the deptsal table; the DBMS interprets the column specification as deptsal.dno

  • dept

    The DBMS Server checks the table specified in the nearest from clause (deptsal). The dept column does not belong to the deptsal table.

    The DBMS Server checks the table specified in the from clause at the next highest level (the employee table). The dept column does belong to the employee table; the column specification is interpreted as employee.dept.

• The DBMS Server does not search across subqueries at the same level to resolve unqualified column names. For example, given the query:

  select * from employee
  where
       dept = (select dept from sales_departments
            where mgrno=manager)
       or
       dept = (select dept from mktg_departments
            where mgrno=manager_id);

The DBMS Server checks the description of the sales_departments table for the mgrno and manager columns; if they are not found, it checks the employee table next, but does not check the mktg_departments table. Similarly, the DBMS Server first checks the mktg_departments table for the mgrno and manager_id columns. If they are not found, it checks the employee table, but never checks the sales_departments table.

Database Procedures

Database procedures are compiled, stored, and managed by the DBMS Server. Database procedures can be used in conjunction with rules to enforce database integrities, or to perform frequently repeated operations. When the procedure is created, its execution plan is saved, reducing the execution overhead.

Database Procedure Creation

Database procedures can be created interactively or in an embedded program. A database procedure can be executed in a host language program, in terminal monitor, in another database procedure, or in a 4GL program. Database procedures can also be invoked by rules. For more information, see Database Procedures and Rules in the chapter "Understanding Database Procedures, Sessions, and Events."

Determine Settings for a Database

To determine the settings for the database to which a session is connected, use dbmsinfo(db_name_case) and dbmsinfo(db_delim_case).

For details about dbmsinfo, see Status Information

Object Management Extension

The Object Management Extension allows data types to be created in addition to the standard SQL data types. Using the Object Management Extension, you can define operators and a function to manipulate your data types, and integrate the new data types, operators, and functions into the DBMS Server.

ANSI Compliance

Ingres is compliant with ANSI/ISO Entry SQL-92. In addition, Ingres contains numerous vendor extensions. For embedded SQL applications, the ESQL preprocessor can be directed to flag statements in your program that are not compliant with entry-level ANSI/ISO SQL-92. For details, see the Embedded SQL Companion Guide.

Information about the settings required to operate in compliance with ANSI/ISO Entry SQL-92, can be found in the appendix "ANSI Compliance Settings."

OpenSQL

OpenSQL is the subset of SQL statements that can be used to access non-Ingres databases through Enterprise Access products.

Security Levels

Basic Ingres installations can be administered in compliance with the C2 security standard. The following statements are of particular interest to C2 security administrators and DBAs:

• Create/drop/help security_alarm
• Enable/disable security_audit
• Create/alter/drop user
• Create/alter/drop role
• Create/alter/drop group
• Create/alter/drop location
• Register/remove table
• Dbmsinfo(security_priv)
• Dbmsinfo(security_audit_log)

For details about administering a C2 site, see the Database Administrator Guide.