《ISO-9075-11-2003.pdf》由会员分享,可在线阅读,更多相关《ISO-9075-11-2003.pdf(311页珍藏版)》请在三一文库上搜索。
1、 ICS 35.060 Ref. No. ISO/IEC 9075-11:2003/Cor.1:2005(E) ISO/IEC 2005 All rights reserved Published in Switzerland INTERNATIONAL STANDARD ISO/IEC 9075-11:2003 TECHNICAL CORRIGENDUM 1 Published 2005-11-15 INTERNATIONAL ORGANIZATION FOR STANDARDIZATION ORGANISATION INTERNATIONALE DE NORMALISATION INTER
2、NATIONAL ELECTROTECHNICAL COMMISSION COMMISSION LECTROTECHNIQUE INTERNATIONALE Information technology Database languages SQL Part 11: Information and Definition Schemas (SQL/Schemata) TECHNICAL CORRIGENDUM 1 Technologies de linformation Langages de base de donnes SQL Partie 11: Schmas des informatio
3、ns et des dfinitions (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
4、 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 omissio
5、n 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
6、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
7、 numbered 4.3.0, 4.3.0a, etc. Copyright International Organization for Standardization Provided by IHS under license with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from IHS -,-,- ISO/IEC 907
8、5-11:2003/Cor.1:2005(E) ii ISO/IEC 2005 All rights reserved Copyright International Organization for Standardization Provided by IHS under license with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without lice
9、nse from IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) ISO/IEC 2005 All rights reserved iii ContentsPage Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 5Information S
10、chema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 5.14COLLATIONS view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11、. . . . 1 5.17COLUMN_DOMAIN_USAGE view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 5.28ELEMENT_TYPES view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
12、. . . . 2 5.31KEY_COLUMN_USAGE view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 5.63TRANSLATIONS view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
13、. . . . . . . 3 5.77Short name views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 6Definition Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
14、. . . . . . . . . . . . . . . . . . . . . . . 5 6.6ATTRIBUTES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 6.7AUTHORIZATIONS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
15、. . . . . . . . . . . . . . . . . . . . . . . . . . . 6 6.27FIELDS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 6.34ROLE_AUTHORIZATION_DESCRIPTORS base table. . . . . . . . . . . . . . . . .
16、. . . . . . . . . . . . . . . . . . . . . . . . 6 6.42SEQUENCES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 6.51TABLES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17、 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 6.66VIEWS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Annex CDeprecated features. . . . . . . . . . . . . . . . . . .
18、 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Annex ESQL feature taxonomy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Copyright International Organization for Standardization
19、 Provided by IHS under license with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) iv ISO/IEC 2005 All rights reserved Tables TablePage 3Feature
20、taxonomy for optional features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Copyright International Organization for Standardization Provided by IHS under license with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie N
21、ot for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) ISO/IEC 2005 All rights reserved 1 TECHNICAL CORRIGENDUM 1 Foreword 1.Rationale:Correct intent of this second edition. Insert the following paragraph after
22、the 5th 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
23、 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. In the 6th paragraph, delete the 5th bullet. 5Information Schema 5.14COLLATIONS view 1.Rationale:Replace Feature F691 with Feature F690. Replace Conformance Rule 1)
24、 with: 1)Without Feature F690, “Collation support”, conforming SQL language shall not reference INFOR- MATION_SCHEMA.COLLATIONS. 5.17COLUMN_DOMAIN_USAGE view 1.Rationale:Add missing Conformance Rule. Insert the following Conformance Rule: 0.1) Without Feature F251, “Domain support”, conforming SQL l
25、anguage shall not reference INFORMA- TION_SCHEMA.COLUMN_DOMAIN_USAGE. Information technology Database languages SQL Part 11: Information and Definition Schemas (SQL/Schemata) Copyright International Organization for Standardization Provided by IHS under license with ISO Licensee=IHS Employees/111111
26、1001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) 2 ISO/IEC 2005 All rights reserved 5.28ELEMENT_TYPES view 1.Rationale:Replace incorrect reference to DTD_IDENTIFIER. Replace the De
27、finition 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_
28、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, MAX
29、IMUM_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 ( SE
30、LECT 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.31KEY_COLUMN_USAGE view 1.Rationale:Fix the invalid brackets in the KEY_COLUMN_USAGE view. In the Definition
31、, 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_USA
32、GE 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 Copyright International Organization for Standardization Provided by IHS under li
33、cense with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) ISO/IEC 2005 All rights reserved 3 WHERE KCU3.CONSTRAINT_CATALOG = CONSTRAINT_CATALOG A
34、ND KCU3.CONSTRAINT_SCHEMA = CONSTRAINT_SCHEMA AND KCU3.CONSTRAINT_NAME = CONSTRAINT_NAME ) = ( SELECT COUNT (*) FROM DEFINITION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 WHERE ( KCU2.TABLE_CATALOG, KCU2.TABLE_SCHEMA, KCU2.TABLE_NAME, KCU2.COLUMN_NAME ) IN ( SELECT CP2.TABLE_CATALOG, CP2.TABLE_SCHEMA, CP2.TABL
35、E_NAME, CP2.COLUMN_NAME FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES AS CP2 WHERE ( CP2.GRANTEE IN ( PUBLIC, CURRENT_USER ) OR CP2.GRANTEE IN ( SELECT ROLE_NAME FROM ENABLED_ROLES ) ) ) AND KCU2.CONSTRAINT_CATALOG = CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA = CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NA
36、ME = CONSTRAINT_NAME ) ) AND CONSTRAINT_CATALOG = ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_CATALOG_NAME ); 5.63TRANSLATIONS view 1.Rationale:Replace Feature F691 with Feature F695. Replace Conformance Rule 2) with: 2)Without Feature F695, “Translation support”, conforming SQL language shall not
37、 reference INFORMATION_SCHEMA.TRANSLATIONS. 5.77Short name views 1.Rationale:Add missing Conformance Rule. Insert the following Conformance Rule: 1.1) Without Feature F251, “Domain support”, conforming SQL language shall not reference INFORMA- TION_SCHEMA.COL_DOMAIN_USAGE. Copyright International Or
38、ganization for Standardization Provided by IHS under license with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) 4 ISO/IEC 2005 All rights reserv
39、ed 2.Rationale:Delete a Conformance Rule which references an incorrect table Delete Conformance Rule 16). 3.Rationale:Add missing Conformance Rules. Insert the following Conformance Rules: 16.1) Without Feature F341, “Usage tables”, conforming SQL language shall not reference the INFOR- MATION_SCHEM
40、A.TRIG_SEQ_USAGE_S view. 16.2) Without Feature F341, “Usage tables”, conforming SQL language shall not reference the INFOR- MATION_SCHEMA.COL_COL_USAGE view. 4.Rationale:Replace Feature F691 with Features F690 and F695. Replace Conformance Rules 19) and 20) with: 19)Without Feature F690, “Collation
41、support”, conforming SQL language shall not reference INFOR- MATION_SCHEMA.COLLATIONS. 20)Without Feature F695, “Translation support”, conforming SQL language shall not reference INFORMATION_SCHEMA.TRANSLATIONS. 5.Rationale:Reference the correct table. Replace Conformance Rule 21) with: 21)Without F
42、eature F696, “Additional translation documentation”, conforming SQL language shall not reference TRANSLATIONS_S.TRANS_SRC_CATALOG, TRANSLA- TIONS_S.TRANS_SRC_SCHEMA, or TRANSLATIONS_S.TRANS_SRC_NAME. 6.Rationale:Add missing Conformance Rules. Insert the following Conformance Rules: 25.1) Without Fea
43、ture S024, “Enhanced structured types”, conforming SQL language shall not reference INFORMATION_SCHEMA.ROL_TAB_METH_GRNTS. 25.2) Without Feature S041, “Basic reference types”, conforming SQL language shall not reference INFORMATION_SCHEMA.REFERENCED_TYPES_S. 25.3) Without Feature S091, “Basic array
44、support”, or Feature S271, “Basic multiset support”, conforming SQL language shall not reference INFORMATION_SCHEMA.ELEMENT_TYPES_S. 7.Rationale:Reference the correct table. Replace Conformance Rule 30) with: 30)Without Feature T011, “Timestamp in Information Schema”, conforming SQL language shall n
45、ot reference INFORMATION_SCHEMA.TRIGGERS_S.CREATED. Copyright International Organization for Standardization Provided by IHS under license with ISO Licensee=IHS Employees/1111111001, User=Wing, Bernie Not for Resale, 04/02/2007 22:00:27 MDTNo reproduction or networking permitted without license from
46、 IHS -,-,- ISO/IEC 9075-11:2003/Cor.1:2005(E) ISO/IEC 2005 All rights reserved 5 8.Rationale:Add missing Conformance Rules. Insert the following Conformance Rules: 30.1) Without Feature T051, “Row types”, conforming SQL language shall not reference INFORMA- TION_SCHEMA.FIELDS_S. 30.2) Without Featur
47、e T175, “Generated columns”, conforming SQL language shall not reference INFORMATION_SCHEMA.COLUMNS_S.IS_GENERATED. 9.Rationale:Add missing Conformance Rule. Insert the following Conformance Rule: 34.1) Without Feature T176, “Sequence generator support”, conforming SQL language shall not reference INFORMATION_SCHEMA.TRIGGER_SEQ_USAGE_S. 10. Rationale:Delete a redundant Conformance Rule. Delete Conformance Rule 39). 11. Rationale:Add missing Conformance Rules. Insert the following Conformance Rules: 41.1) Without Feature T272, “Enhanced savepoint management”, conforming SQL language shall not