ISO/IEC 10728:1993
(Main)Information technology — Information Resource Dictionary System (IRDS) Services Interface
Information technology — Information Resource Dictionary System (IRDS) Services Interface
The services interface specified gives any program full access to all IRDS services. Defines the semantics of this interface, and also specifies the language bindings for ISO Pascal (ISO 7185). Language bindings for other ISO standard programming languages are provided as separate standards. Makes no assumptions about an implementation environment, and assumes no specific run-time or compile time interfaces. Details of the IRDS series of standards are to be found in ISO/IEC 10027.
Technologies de l'information — Interface de services du gestionnaire de ressources du système d'informations (IRDS)
General Information
Relations
Standards Content (Sample)
INTERNATIONAL
lSO/IEC
STANDARD
10728
First edition
1993-04-15
Information technology - Information
Resource Dictionary System (IRDS)
Services Interface
Technologies de I’informa tion - Interface de Services du gestionnaire de
ressources du Systeme d’informations (IRDS)
Reference number
ISO/IEC 10728:1993(E)
---------------------- Page: 1 ----------------------
ISO/IEC 10728: 1993 (E)
Table of Contents
vi
Forew ord
Vii
Introduction
1
1 scope
1
Normative references
2
2
3 Definitions and abbreviations
3.1 Terms defined or referenced in the IRDS Framework (ISO/IEC 10027)
2
and used in this International Standard
2
3.2 Terms defined in this International Standard
4
3.3 Data Item Name abbreviations
5
4 Conventions
5
4.1 Specification of concepts and facilities
5
4.2 Specification of data structures
5
4.3 Specification of constraints - overview
5
4.4 Specification of Service data structures
5
4.5 Specification of Services
5
4.6 Data Structure Diagmm
5
4.7 Specification of constraints - detail
5
4.7.1 Types of constraint
5
4.7.2 Overview of referential constraints
6
4.7.3 Optional one-to-many referential constraint
7
4.7.4 Required uni-directional one-to-many referential constraint
7
Required uni-directional one-to-one referential constraint
4.7.5
7
Self-referencing tables
4.7.6
7
Required bi-directional referential constraint
4.7.7
8
Mutually-exclusive referential constraints
4.7.8
9
4.7.9 Subtables
10
4.7.10 Principles for expressing constraints
10
4.8 Working Set Diagrams
11
5 IRDS concepts and facilities
11
5.1 IRDS Environment concepts
11
5.2 Categories of table
12
5.3 Overview of IRD Definition tables
0 ISQ/IEC 1993
All rights reserved. No part of this publication may be reproduced or utilized in any form or by
any means, electronie or mechanical, including photocopying and microfilm, without Permission
.- -
in writing from the publisher.
ISOAEC Copyright Office l Case Postale 56 l CH-1 211 Genbve 20 l Switzerland
Printed in Switzerland
---------------------- Page: 2 ----------------------
ISO/IEC 10728: 1993 (E)
0 ISO/IEC
15
5.4 Overview of IRD tables
15
Overview
5.4.1
17
Intemal and common tables
5.4.2
17
IRD-specific tables
5.4.3
17
5.5 Data and the objects to which the datarefers
17
Definition objects comprising data modehing facility
5.5.1
18
Definition objects dependent on an IRD Schema Group
5.5.2
18
Content of IRD tables
5.5.3
18
Accessibility of tables to users
5.5.4
18
5.6 Version Control concepts
18
Objects and Versions of Objects
5.6.1
18
Working Sets
5.6.2
19
Working sets and users
5.6.3
19
Basing one working set on another
5.6.4
20
Materialization of a working set
5.6.5
20
5.6.6 References from one working set to another
22
References to multiple Versions of an Object
5.6.7
22
5.6.8 Context
23
5.6.9 IRD content status
23
5.6.10 References in the IRD
23
5.6.11 Granularity of Version Control
23
5.6.12 Access control
23
5.7 Naming facilities
23
5.7.1 Names
24
5.7.2 IRDS names
24
Variation name
5.7.3
24
5.7.4 Working set name and working set version name
24
5.8 Definable limits and installation defaults
24
Implementation-defined limits
5.8.1
24
5.8.2 Installation defaults
24
5.9 Creating and dropping IRDs
24
5.10 IRD Schema modification
24
5.11 Other added value functionality
24
Audit attributes
5.11.1
IRDS content modules 25
5.11.2
25
5.11.3 System-maintained values
27
6 Abstract data structures
27
6.1 IRD Definition Level
27
6.1.1 IRD Definition Level data structure
27
6.1.2 IRD Definition Level Schema
27
6.1.2.1 Schema IRD Definition
27
6.1.3 IRD Definition Level Domains
27
6.1.3.1 Domain SQL Name
28
6.1.3.2 Domain IRDS Key
28
6.1.3.3 Domain Char Data
28
6.1.3.4 Domain Cardinal
29
6.1.3.5 Domain Boolean
29
6.1.4 IRD Definition Level Tables
29
6.1.4.1 Table IRD Object
30
6.1.4.2 Table IRD Working Set
31
6.1.4.3 Table IRD Object Version
33
6.1.4.4 Table IRD Reference Path
33
6.1.4.5 Table IRDS User
34
6.1.4.6 Table Implementation Limits
35
6.1.4.7 Table IRDS Dictionary
36
6.1.4.8 Table IRD Schema Group
37
6.1.4.9 Table IRD Schema
37
6.1.4.10 Table IRD SchemaReference
38
6.1.4.11 Table IRD Data Type Descriptor
39
6.1.4.12 Table IRD Domain
. . .
lll
---------------------- Page: 3 ----------------------
0 ISO/lEc
ISO/IEC 10728: 1993 (E)
41
6.1.4.13 Table IRD Table
42
6.1.4.14 Table IRD View
43
6.1.4.15 Table IRD Column
45
6.1.4.16 Table IRD View Table Usage
46
6.1.4.17 Table IRD View Column Usage
47
6.1.4.18 Table IRD Table Constraint
48
6.1.4.19 Table IRD Key Column Usage
50
6.1.4.20 Table IRD Referential Constraint
51
6.1.4.21 Table IRD Check Constraint
53
6.1.4.22 Table IRD Check Table Usage
54
6.1.4.23 Table IRD Check Column Usage
54
6.1.4.24 Table IRD Assertion
55
6.1.4.25 Table IRD Module
56
6.1.4.26 Table IRD Content Status
57
6.1.4.27 Table Installation Default
57
6.1.4.28 Table IRD Working Set Privilege
59
6.1.5 IRD Definition Level Views
59
6.1.5.1 View All SQL Names
60
6.1.5.2 View IRD Object Version
60
6.1.5.3 View IRD Working Set
60
6.1.5.4 View IRD Reference Path
61
IRD Definition Level Change Control
6.1.6
61
6.1.7 IRD Definition Level Initial Contents
63
6.2 IRD Level
63
RD Level data structure
6.2.1
63
IRD Level Initial Contents
6.2.2
65
6.3 IRD General Rules
65
Use of primary key
6.3.1
66
6.3.2 References and content Status
66
Resolution of references
6.3.3
66
Resolution of references within a version path
6.3.4
66
References depending on a reference path
6.3.5
66
Reference paths and Version paths
6.3.6
67
Services concepts and facilities
7
67
7.1 Levels and parallelism
67
7.2 Access to IRDS data via Database Services Processor
67
7.2.1 Prevention of cinxmvention of IRDS security and integrity
67
7.2.2 Access to IRDS Data using a Standard Database Language
67
7.3 Connecting an application to the IRDS Services Interface Processor
67
7.3.1 Sessions and transactions
68
IRDS users and Privileges
7.3.2
68
7.4 Object selection
68
7.5 Sets and cufsors
68
7.6 Diagnos tics
69
7.7 Version control
69
7.8 Operations on Abstract Data Structures
71
8 Service data structures
71
8.1 Basic data constants
71
8.1.1 Name Length Limits
71
8.1.2 Attribute Length Limits
71
8.1.3 Control ldentifier Length Limits
71
8.1.4 Data Types
72
8.1.5 IRD Content Status Classes
72
8.1.6 Close Type Parameter
72
8.2 Service data types
iv
---------------------- Page: 4 ----------------------
0 ISO/IEC ISO/IEC 10728: 1993 (E)
72
8.2.1 Column data types
73
8.2.2 Object Names
73
8.2.3 Control Identifiers
73
8.2.4 Diagnostics Area
73
8.2.5 Service Return Code
74
8.2.6 Column List Parameters
75
9 Service Formats and Descriptions
75
9.1 Operational Services
Create IRD Definition Service 75
9.1.1
9.1.2 Drop IRD Definition Service 75
Open IRDS Service 76
9.1.3
9.1.4 Prepare Service 77
9.1.5 Commit Service 77
9.1.6 Rollback Service 78
Close IRDS Service 78
9.1.7
Get Diagnostics Service 78
9.1.8
79
9.2 Level independent services
79
9.2.1 Set Context Service
79
9.2.2 Add Object Service
Open Cursor Service 81
9.2.3
Retrieve Object Service 82
9.2.4
F
83
92 -3 Modify Object Service
Delete Object Service 84
9’2 . . 6
b
Declassify Object Service 8s
92 .7
86
9’2 e I +i Reclassify Object Service
87
9’2 q Glose Cursor Service
Create Working Set Service 87
9:2h
88
9.2.11 Drop Working Set Service
89
9.2.1.L odify Content Status Service
90
9.2.13 Create Reference Path Service
90
9.2.M Modify Weference Path Service
91
9.2.15 Dmp Reference Path Service
92
Definition4 Level specific services
92
Create IRD Service
Drop RD Service 92
93
Deactivate IRD Service
93
Reactiva D Service
Validate Schema Group Service 94
9.4 Sequence sf permitted Service invocation 95
9.4.1 Specification of valid sequences of IODS Service invocations 95
9.4.2 General rules 95
10 Conformance 97
Annexes
99
A - State classes and subclasses
99
A.1 State classes
100
A.2 State subclasses
101
A.3 State record
B - User-defined tables 103
---------------------- Page: 5 ----------------------
ISO/IEC 10728: 1993 (E)
0 ISO/IEC
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission) form the
specialized System for worldwide standardization. National bodies that are members of ISO or IEC participate in the
development of International Standards through technical committees established by the respective organization to deal
with particular Gelds of technical activity. ISO and IEC technical committees collaborate in Gelds of mutual interest. Other
international organizations, govemmental and non-govemmental, in liaison with ISO and IEC, also take part in the work.
In the field of information technology, ISO and IEC have established a joint technical committee, ISO/IEC JTC 1. Draft
International Standards adopted by the joint technical committee arc circulated to national bodies for voting. Publication as
an International Standard requires approval by at least 75 % of the national bodies casting a vote.
International Standard ISO/IEC 10728 was prepared by Joint Technical Committee ISO/IEC JTC 1, Infomtion teclz-
nology, Sub-Committee SC 21, Infomtion retrieval, transfer- and mmuzgement for open Systems interconnection (OSI).
Annex A forms an integral part of this International Standard. Annex B is for information only.
vi
---------------------- Page: 6 ----------------------
ISO/IEC 10728: 1993 (E)
0 ISO/IEC
Introduction
This International Standard is one of a series of International Standards on Information Resource Dictionary Systems.
ISO/IEC 10027 defines the context within which this International Standard is to be applied.
vii
---------------------- Page: 7 ----------------------
0 ISO/IEC
ISO/IEC 10728: 1993 (E)
. . .
Vlll
---------------------- Page: 8 ----------------------
INTERNATIONAL STANDARD 0 ISO/IEC
ISOiIEC 10728:1993 (E)
Information technology - Information Resource Dictionary
System (IRDS) Services Interface
2
1 Scope Normative references
The IRDS series of International Standards specifies a The following Standards containprovisions which, through
reference in this text, constitute provisions of this
Software tool that tan be used to describe and potentially
International Standard. At the time of publication, the
control an enterprise’s information resources. It defines the
editions indicated were valid. All standards are subject to
structure and part of the content of the data to be maintained
at the IRD Definition Level, and the structure of the data revision, and Parties to agreements based on this
International Standard are encouraged to investigate the
to be maintained at the IRD Level. It also defines the
Services to be provided for maintaining and retrieving data possibility of applying the most recent editions of the
Standards listed below. Members of IEC and ISO maintain
at both levels. Further details of the IRDS series of
Standards are to be found in ISO/IEC 10027. registers of currently valid International Standards.
This International Standard specifies a Services Interface ISO 3 166: 1988; Codes for the representation of names of
that gives any program full access to all IRDS services, countries
through whatever extemal cal1 interface is provided by the
language in which the program is written. The body of this ISO 7 185: 1990; Information Technology - Programming
International Standard defines the semantics of this
languages - Pascal.
interface, and also specifies the language bindings for ISO
Pascal (ISO 7185). Language bindings for other ISO
ISO/lEC 9075: 1992; Information Technology - Database
Standard programming languages are provided as separate
Languages - SQL.
Standards.
ISO/IEC 10027: 1990; Information Technology -
This International Standard makes no assumptions about
Information Resource Dictionary System (IRDS) -
an implementation environment, and assumes no specific
Framework.
nm-time or compile-time interfaces.
ISO/IEC 10032: 1993; Information Technology -
Reference Model of Data Management
---------------------- Page: 9 ----------------------
0 ISO/IEC
ISO/IEC 10728: 1993 (E)
3.2.6 context: A working set established by default or by
Definitions and abbreviations
3
user request within which IRDS Services are performed.
NOTE - SQL tenns a= not defiied hexe. When used in this International
Standard, they have the meanings ascribed to them in ISO/IEC 9075. All
3.2.7 controlled: A content Status class that indicates data
IRDS tenns used in this International Standard are fully defined hexe or
that is stable and not subject to Change.
in ISO/IEC 10027.
3.2.8 definition Object: An Object recorded at the IRD
definition level that controls the data which may be present
3.1 Terms defined or referenced in the IRDS
at the IRD level.
Framework (ISO/IEC 10027) and used
in this International Standard
3.2.9 dictionary: An IRD Definition or IRD
The following terms are defined (or referenced) and used
3.2.10 environment table: A table that exists once in each
in the IRDS Framework. They are used in the Same way in
IRD Definition, controlling the Services provided on that
this International Standard.
IRD Definition and any associated IRDs.
3.1.1 client
3.2.11 implementation-defined: Behaviour not defined by
this International Standard, but which shall be precisely
3.1.2 Information Resource Dictionary (IRD)
defined by any conforming implementation
3.1.3 Information Resource Dictionary System (IRDS)
3.2.12 implementation-dependent: Behaviour not defined
by this International Standard, and which an
3.1.4 IRD definition
implementation is not required to define. Further, there is
no requirement that such behaviour be consistent ffom case
3.1.5 IRD definition level
to case.
3.1.6 IRD definition Schema
3.2.13 intemal table: A table that exists once in each IRD
Definition and each IRD, rows in which cannot be accessed
3.1.7 IRD level
by the Object-related services in clause 9.
3.1.8 IRD Schema
3.2.14 IRD-specific table: A table that exists only in the
IRD Definition or a specific IRD, as part of the
3.1.9 level pair
representation of the data structuring rules of a defined data
modehing facility.
3.1.10 real System
3.2.15 IRD content Status: A user-defined attribute of a
3.1.11 Service
working set. Every value of IRD content Status belongs to
one of the three predefined content Status classes. Esch
Object version takes its IRD content Status from the
3.2 Terms defined in this International
working set that contains it.
Standard
3.2.16 IRD content Status class: One of three predefined
Where each term listed in this clause is introduced in a later
sets of IRD content statuses: uncontrolled, controlled and
clause of this International Standard, it is printed in bold
archived.
wpe*
3.2.17 IRD Object: An Object recorded at the IRD level.
3.2.1 active: A state in which a dictionary is accessible to
all relevant IRDS Services. When an IRD is not active, only
3.2.18 IRD Schema Group: A collection of one or more
the Reactivate IRD Service is applicable.
IRD Schemas that completely defines what may exist at
any time in an IRD.
3.2.2 archived: A content Status class that indicates data
that is no longer in active use.
3.2.19 IRDS database: An IRD definition and zero or
more IRDs.
3.2.3 attribute: A characteristic of an Object.
3.2.20 IRDS environment: An operational instance of an
3.2.4 common table: A table that exists in each IRD
implementation of the IRDS Services Interface managing
Definition and each IRD.
an IRDS database.
3.2.5 content module: A collection of objects introduced
into an IRD Definition or XRD at the same time and from
the source, identified by a module name that indicates the
Source of the module.
3
---------------------- Page: 10 ----------------------
ISO/IEC 10728:1993 (E)
0 ISO/IEc
3.2.32 reference path: A directed association from one
3.2.21 IRDS name: A name optionally assigned when an
working set to another which allows an Object Version in
Object is added to an IRD or a definition Object is added to
the first working set to reference Object Versions in the
an IRD definition. If specified, the combination of IRDS
second working set. A reference path aIlows references
name, Variation name, working set name and working set
only in the direction in which it is specified.
version name shaIl be unique.
3.2.33 referenced table: The table to which the reference
3.2.22 IRDS session: A temporary association between an
is made in a referential constmint.
IRDS user and an IRDS environment, during which the
former requests Services and the latter performs them.
3.2.34 referencing table: The table from which the
3.2.23 IRDS User: An individual or group authorized to reference is made in a referential constraint.
use the IRDS.
3.2.35 subtable: Let SUBT and SUPERT be tables. SUBT
3.2.24 level independent service: A Service that is equally is defined to be a subtable of SUPERT if and only if every
applicable to the IRD Definition level and the IRD level. row of SUBT corresponds to one and only one row of
SUPmT, and each row of SUPERT corresponds to at most
one row of SUBT. SUPERT is defined to be a supertable
3.2.25 level specific Service: A Service which only applies
of SUBT.
either to the IRD Definition level or to the IRD level, but
not both.
3.2.36 supertable: A table that has at least one subtable
3.2.26 materialization (of a working Set): That collection (see definition of subtable).
of Object Versions that tan be in the working table of a
cursor opened on that working set. 3.2.37 uncontrolled: A content Status class that indicates
data that is not stable.
3.2.27 name: A string of characters used to distinguish
objects, either alone or i n combination with other names. 3.2.38 Variation name: A name used to identify
significantly different variants of objects with the same
3.2.28 non-versionable (of an Object type): Indicates that RDS name.
only one version of an Object of that type may exist at any
time, in a non-versionable working set; of a working set, 3.2.39 versionable (of an Object type): Indicates that more
indicates that the working set may not be based on another than one Version of an Object of that type may exist at the
working set or be used as the basis foranother working set. Same time, in different working Sets; of a working set,
indicates that the working set may not contain objects of
non-versienable Object types, may be based on another
3.2.29 Object: concept or thing of interest to an
AnY
working set and may be used as the basis for another
enterprise.
working set.
3.2.30 Object type: A class of objects all of whose attributes
3.2.40 working set: A collection of Versions of either
belong to a common set of attribute types.
definition objects or IRD objects, defined by an IRDS user
as a unit for the purposes of Change management, content
3.2.31 Object Version: A record of the information about
Status specification and access control.
an Object that is current for some period of time in some
information processing context.
---------------------- Page: 11 ----------------------
0 ISO/IEC
ISO/EC 10728: 1993 (E)
Data Item Name abbreviations
3.3
The following list describes all of the abbreviations that are
used in naming the columns of the SQL tables and the
corresponding Pascal constants, types and variables:
= added
Add
= archived
Arch
Ati- = attribute
= class
Cis
Cntl = controlled
Col = column
= cursor
Cur
= current
Curr
Des = IRD content status
= definition
Def
Dflt = default
Dflts = defaults
Die = dictionary
Dom = domain
= identifier
Id
= implementation
Imp
Ind = indicator
= installation
Inst
= integer
Int
Ird = Information Resource Dictionq
Len = length
= limit
Lim
Main t = maintained
= maximum
Max
Min = minimum
Mod = modified
= national
Nat
Num = number
.
= Object
QbJ
= reference
Ref
Ret = return
Sem = schema
= separater
SeP
= session
Sess
= specification
spec
SN = service
= stling
St.l=
= transaction
Tran
Txt = text
Ucntl = uncontrolled
= value
Val
= Variation
Var
Ver = Version
= working
Wkg
= working set
Ws
---------------------- Page: 12 ----------------------
ISO/IEC 10728: 1993 (E)
0 ISO/IEC
4.5 Specification of services
4 Conventions
In clause 9, the Services to be supported by an IRDS arc
This clause describes the conventions used within this
specified using a combination of Pascal (ISO 7185) and
International Standard to describe the facilities of an IRDS.
English.
These conventions are not themselves a subject of this
Standard. SeveraI different specification conventions arc
used, for different purposes.
4.6 Data Structure Diagrams
The Data Structure Diagams used in clause 5 illustrate
4.1 Specification of concepts and facilities
tables and the constraints between the tables. A table is
Within clause 5, diagrams arc used, in conjunction with rePresented bY a rectangularbox.
English description, to introduce some of the concepts and
Constmints between the tables are represented by lines
facilities that are formally defined later in this International
between the boxes. Esch line is considered to have two
Standard. Two types of diagram are used:
halves, each half associated with the box to which it is
directly connected.
a) Data Structure Diagrams, described in 4.6;
b) Working Set Diagrains, described in 4.8.
4.7 Specification of constraints - detail
4.2 Specifkation of data structures
4.7.1 Types of constraint
In clause 6, the data structures to be managed by an IRDS
The following types of constmint are used within the
Services Processor are specified using Database Language
formal data specification in clause 6:
SQL (ISO/IEC 9075).
a) Primary key constmints - which identify the primary
In general, this involves the use of tables to represent Object
key of each table.
types and columns to represent attribute types; certain data
is also required for control purposes.
b) Uniqueness constraints - which identify
combinations of columns within a table whose
The use of SQL as a definition formalism in this
values must be unique, when not wholly or partially
International Standard does not imply any specific
null.
implementation approach. The user of the Services
provided at the IRDS Services Interface sees the data only
c) Referential constraints - which identify the
in the terms defined in clause 8.
dependencies of one table on another.
d) Check constraints - which allow the specification of
4.3 Specification of constraints - overview
many additional general constraints on the values or
combinations of values which may appear in one or
Constmints on the possible values, or combinations of
more rows in one or more tables.
values, which may appear in the IRD are specified once
only wherever possible. In Order of preference, each
In addition to their specification in clause 6, referential
constraint is specified:
constraints are also illustrated diagmmmatically in clause
5. The rest of this clause describes the diagramming
a) within the formal data specification, in clause 6;
conventions used for different types of referential
constraint and the SQL syntax used to represent each type.
b) in the description of the relevant table, in clause 6;
4.7.2 Overview of referential constraints
c) in the description of the Services that tan be used to
Change the data, in clause 9.
It is necessary to explain the type of referential constrGnts
used before describing their representation.
The specification of constraints is described in detail in 4.7.
A referential constraint exists between two tables A and B
if at all times the values of some specified column or
4.4 Specification of Service data structures
combination of columns, when not null, in each row in
table B shall be equal to the values of a corresponding
In clause 8, the formats of the data structures associated
combination (in terms of number and data type) of columns
with each Service are described using Pascal (ISO 7185).
in one and only one row in table A. Such constmints arc
further qualified as follows:
---------------------- Page: 13 ----------------------
0 ISO/IEC
ISO/IEC 10728: 1993 (E)
if only a Single row in table B may
a) One-to-one -
reference any one row in table A.
b) One-to-many - if more than one row in table B tan
reference the same row in table A.
Orthogonal to the above classification of constraints, the
following classification may also be made:
Figure 1 - Optional referential constraint
c) Optional at referencing table - if any of the columns
in table B, which are used to reference table A, may
Table 1 Shows the corresponding SQL Syntax.
be null; a row in which any of these columns is null
is considered not to reference table A;
Table 1: SQL corresponding to Figure 1
d) Optional at referenced table - if a row may exist in
table A without being referenced from any row in
CREATE TABLE A
table B;
(Al IRDS KEY PRIMARY KEY)
-
e) Required at referencing table - if the referencing
CREATE TABLE B
columns in table B may not be null. Every row in
(BI IRDS KEY PRIMARY KEY,
-
table B must reference a row in table A.
82 IRDS KEY
-
f) Required at referenced table - if arow in table A may
exist only if there is a corresponding row in table B
CONSTRAINT constraint-name
that references it. REFERENCES A
ON DELETE referential-action )
The following basic constructs are used within the Data
Structure Diagram to illustrate these constraints. Esch half
of the line that represents the constraint is treated separately
In table 1 and similar tables, IRDS KEY represents the
in illustrating the characteristics of the constraint at each
name of a domain (see ISO/IEC 9075) which is used
table.
throughout the tables defined in clause 6. It is used here
solely to make these examples look as similar as possible
a) Solid line - the constraint is required at that table; to the SQL used in clause 6.
The SQL syntax used in clause 6 will normally contain an
b) Dashed line - the constraint is optional at that table;
ON DELETE clause, similar to that shown in Table 1. This
clause specifies additional information that is not shown in
“Crow’s feet” at end of line - represents a
C>
the diagram - namely the action to be taken when an attempt
one-to-many constraint. (Crow’s feet are not
is made to delete a row in table A that is referenced by a
allow ed at both ends of a line.)
row in table B.
d) No “crow’s feet” at either end - represents a
For an optional referential constraint, as shown in table 1,
one-to-one constraint.
the referential action tan be one of the following:
The diagrammatic representations of some of the various
a) RESTRICT - (cannot be specified explicitly, but is
constraint types listed above are now illustrated. In each
implied if ON DELETE clause is not specified)
case, an example is also given of how the relevant
which means the deletion will not be allowed;
constraint would be expressed in clause 6, using SQL with
or without accompanying English description.
b) CASCADE - which means the referencing rows
will also be deleted.
4.73 Optional one-to-many referential constraint
c) SET NULL - which means the referencing
Figure 1 illustrates the simplest (in terms of its SQL
columns will be nullified, thus removing the
specification) constraint - an optional one-to-many
reference, but the row itself will not be deleted.
referential constraint.
6
---------------------- Page: 14 ----------------------
ISO/IEC 10728:1993 (E)
0 ISO/IEc
4.7.4 Required uni-directional one-to-many referential Table 3 Shows an example of the SQL syntax to specify the
constraint
constraint illustrated in figure 3. The addition of a
uniqueness constraint on column B2 enforces the
Figure 2 illustrates a required uni-directional one-to-many
sinnularitv of the constraint.
referential constraint. The solid half of the line connected
to box B illustrates that this constraint is required from B
Table 3 - SQL corresponding to figure 3
to A.
CREATE TABLE A
(Al IRDS KEY PRIMARY KEY)
-
CREATE TABLE B
(Bl IRDS KEY PRIMARY KEY,
-
B2 IRDS KEY NOT NULL
CONSTl%!hNT constraint-name
REFERENCES A
ON DELETE referential-action,
Figure 2 - Required referential constraint (one to
many) UNIQUE (B2) )
Table 2 Shows an example of the SQL syntax to specify the
constraint illustrated in figure 2. The addition of the NOT
4.7.6 Self-referencing tables
NULL clause on the referencing column B2 makes the
constraint required from table B to table A.
Table 2 - SQL corresponding to figure 2
CREATE TABLE A
(Al IRDS KEY PRIMARY KEY)
-
Figure 4 - Self-referencing constraint
CREATE TABLE B
Note that in any of the above cases A and B may be the
(BI IRDS KEY PRIMARY KEY,
-
same table, in which case the line representing the
B2 IRDS KEY NOT NULL constraint is drawn as a circular arc between two points on
-
the same box, as in figure 4, and the SQL referential
CONSTRAINT constraint-name
constraint is between two columns, or groups of columns,
REFERENCES A
in the same table, as in table 4.
ON DELETE referential-action )
Table 4: SQL corresponding to Figure 4
Because of the NOT NULL clause, SET NULL is not a
valid referential action for a required referential constraint.
CREATE TABLE A
(Al IRDS KEY PRIMARY KEY
-
4.7.5 Required uni-directional one-to-one referential
A2 IRDS KEY
constraint -
CONSTRAINT constraint-name
REFERENCES A
ON DELETE refer
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.