ISO/IEC 9075-11:2003/Cor 1:2005

INTERNATIONAL STANDARD ISO/IEC 9075-11:2003
TECHNICAL CORRIGENDUM 1
Published 2005-11-15
INTERNATIONAL ORGANIZATION FOR STANDARDIZATION • МЕЖДУНАРОДНАЯ ОРГАНИЗАЦИЯ ПО СТАНДАРТИЗАЦИИ • ORGANISATION INTERNATIONALE DE NORMALISATION
INTERNATIONAL ELECTROTECHNICAL COMMISSION • МЕЖДУНАРОДНАЯ ЭЛЕКТРОТЕХНИЧЕСКАЯ КОМИССИЯ • COMMISSION ÉLECTROTECHNIQUE INTERNATIONALE


Information technology — Database languages — SQL —
Part 11:
Information and Definition Schemas (SQL/Schemata)
TECHNICAL CORRIGENDUM 1
Technologies de l'information — Langages de base de données — SQL —
Partie 11: Schémas des informations et des définitions (SQL/Schemata)
RECTIFICATIF TECHNIQUE 1
Technical Corrigendum 1 to ISO/IEC 9075-11:2003 was prepared by Joint Technical Committee
ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange.

Statement of purpose for rationale
A statement indicating the rationale for each change to ISO/IEC 9075-11:2003 is included. This is to inform the users of
ISO/IEC 9075-11:2003 why it was judged necessary to change the original wording. In many cases, the reason is editorial
or to clarify the wording; in some cases, it is to correct an error or an omission in the original wording.
Notes on numbering
Where this Technical Corrigendum introduces new Syntax, Access, General, and Conformance Rules, the new rules
have been numbered as follows:
Rules inserted between, for example, Rules 7) and 8) are numbered 7.1), 7.2), etc. [or 7)a.1), 7)a.2), etc.]. Those
inserted before Rule 1) are numbered 0.1), 0.2, etc.
Where this Technical Corrigendum introduces new subclauses, the new subclauses have been numbered as follows:
Subclauses inserted between, for example, 4.3.2 and 4.3.3 are numbered 4.3.2a, 4.3.2b, etc. Those inserted before,
for example, 4.3.1 are numbered 4.3.0, 4.3.0a, etc.
ICS 35.060 Ref. No. ISO/IEC 9075-11:2003/Cor.1:2005(E)
©  ISO/IEC 2005 – All rights reserved
Published in Switzerland

---------------------- Page: 1 ----------------------
ISO/IEC 9075-11:2003/Cor.1:2005(E)
ii ©  ISO/IEC 2005 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC 9075-11:2003/Cor.1:2005(E)
Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
5 Information Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
5.14 COLLATIONS view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
5.17 COLUMN_DOMAIN_USAGE view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
5.28 ELEMENT_TYPES view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
5.31 KEY_COLUMN_USAGE view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
5.63 TRANSLATIONS view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
5.77 Short name views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
6 Definition Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
6.6 ATTRIBUTES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
6.7 AUTHORIZATIONS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
6.27 FIELDS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
6.34 ROLE_AUTHORIZATION_DESCRIPTORS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
6.42 SEQUENCES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
6.51 TABLES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
6.66 VIEWS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Annex C Deprecated features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Annex E SQL feature taxonomy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
©  ISO/IEC 2005 – All rights reserved iii

---------------------- Page: 3 ----------------------
ISO/IEC 9075-11:2003/Cor.1:2005(E)
Tables
Table Page
3 Feature taxonomy for optional features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
iv ©  ISO/IEC 2005 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC 9075-11:2003/Cor.1:2005(E)

Information technology — Database languages — SQL —
Part 11:
Information and Definition Schemas (SQL/Schemata)
TECHNICAL CORRIGENDUM 1
Foreword
1. Rationale:Correct intent of this second edition.
th
Insert the following paragraph after the 5 paragraph:
This first edition of ISO/IEC 9075-11, together with ISO/IEC 9075-2:2003, cancels and replaces
ISO/IEC 9075-2:1999 and ISO/IEC 9075-5:1999, which have been technically revised. It also incorporates
the relevant parts of Amendment ISO/IEC 9075-2:1999/Amd.1:2001 and of the Technical Corrigenda
ISO/IEC 9075-2:1999/Cor.1:2000 and ISO/IEC 9075-2:1999/Cor.2:2003.
2. Rationale:Remove incorrect reference to obsolete part.
th th
In the 6 paragraph, delete the 5 bullet.
5 Information Schema
5.14 COLLATIONS view
1. Rationale:Replace Feature F691 with Feature F690.
Replace Conformance Rule 1) with:
1) Without Feature F690, “Collation support”, conforming SQL language shall not reference INFOR-
MATION_SCHEMA.COLLATIONS.
5.17 COLUMN_DOMAIN_USAGE view
1. Rationale:Add missing Conformance Rule.
Insert the following Conformance Rule:
0.1) Without Feature F251, “Domain support”, conforming SQL language shall not reference INFORMA-
TION_SCHEMA.COLUMN_DOMAIN_USAGE.
©  ISO/IEC 2005 – All rights reserved 1

---------------------- Page: 5 ----------------------
ISO/IEC 9075-11:2003/Cor.1:2005(E)
5.28 ELEMENT_TYPES view
1. Rationale:Replace incorrect reference to DTD_IDENTIFIER.
Replace the Definition with:
CREATE VIEW ELEMENT_TYPES AS
SELECT DISTINCT
    OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
    OBJECT_TYPE, COLLECTION_TYPE_IDENTIFIER, DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME,
    NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
    DATETIME_PRECISION, INTERVAL_TYPE, INTERVAL_PRECISION,
USER_DEFINED_TYPE_CATALOG AS UDT_CATALOG,
USER_DEFINED_TYPE_SCHEMA AS UDT_SCHEMA,
USER_DEFINED_TYPE_NAME AS UDT_NAME,
SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_NAME,
    MAXIMUM_CARDINALITY, DTD_IDENTIFIER
FROM DEFINITION_SCHEMA.ELEMENT_TYPES AS E
JOIN
     DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D
USING ( OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
       OBJECT_TYPE, DTD_IDENTIFIER )
WHERE ( OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
      OBJECT_TYPE, ROOT_DTD_IDENTIFIER ) IN
     ( SELECT OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
          OBJECT_TYPE, DTD_IDENTIFIER
FROM INFORMATION_SCHEMA.DATA_TYPE_PRIVILEGES );
GRANT SELECT ON TABLE ELEMENT_TYPES
TO PUBLIC WITH GRANT OPTION;
5.31 KEY_COLUMN_USAGE view
1. Rationale:Fix the invalid brackets in the KEY_COLUMN_USAGE view.
In the Definition, replace the view definition with:
CREATE VIEW KEY_COLUMN_USAGE AS
SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
     KCU1.TABLE_CATALOG, KCU1.TABLE_SCHEMA, KCU1.TABLE_NAME,
     KCU1.COLUMN_NAME, KCU1.ORDINAL_POSITION, KCU1.POSITION_IN_UNIQUE_CONSTRAINT
FROM DEFINITION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
USING ( CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME )
WHERE ( ( SELECT MAX ( KCU3.ORDINAL_POSITION )
FROM DEFINITION_SCHEMA.KEY_COLUMN_USAGE AS KCU3
2 ©  ISO/IEC 2005 – All rights reserved

---------------------- Page: 6 ----------------------
ISO/IEC 9075-11:2003/Cor.1:2005(E)

WHERE KCU3.CONSTRAINT_CATALOG = CONSTRAINT_CATALOG
AND
         KCU3.CONSTRAINT_SCHEMA = CONSTRAINT_SCHEMA
AND
         KCU3.CON
...