Oracle XML DB is a set of
built-in high-performance storage and retrieval technologies developed
especially for XML. It enables you to
constrain XML data according to XML schema definitions and hence achieve
control over relationships that structured data has always enjoyed.
Alongside its other features, Oracle XML DB allows to define constraints
on XMLType tables and columns which are based on XML schemas.
Constraint definition plays
an important role in all database transactions by helping maintain the
integrity of the data stored inside the database.
This document, describes how to specify constraints between two schema-based
XMLType tables and also between an XMLType table and a relational table.
As an example here, we are considering schema-based
XMLType tables. Creating constraints for schema-based XMLType columns
is similar to how it is done for XMLType tables.
To run the SQL scripts described in this document, connect
to an Oracle database from a Sql*Plus client as any user. For example:
SQL> CONNECT scott/tiger@orcl
Ensure to use the same database schema for running all
the scripts provided in this document.
Note: In this document, for simplicity, schema creation and sample
data insertion scripts use small size XML documents. In PL/SQL, there
is a limitation of 64K on the size of a String constant.
If an XML document greater than 64K is required, the best approach is
to load the document from a file. For details on this approach, please
refer to the "Oracle XML DB Utilities Package" in the XML DB Sample
Corner at /sample_code/tech/xml/xmldb/index.html.
Here we will look at defining constraints between
two XMLType tables. Consider the following scenario:
We have two schema-based XMLType tables: DEMO_CUSTOMERS
and DEMO_ACCOUNTS.
The DEMO_CUSTOMERS table contains
all the customer details and the DEMO_ACCOUNTS table
contains all the account details. Both these tables need a Unique
key defined on them.
The DEMO_ACCOUNTS table contains
the account details of only those customers whose details exist in
the DEMO_CUSTOMERS table. Hence there exists a master-detail
relationship between these two tables. Thus a foreign key needs to
be defined between these two tables.
Next we will look at how to create these schema-based tables, specify
constraints on them, and check the constraint definition by inserting
sample data into the table.
Step 1: Register the XML Schemas for the XMLType
Demo Tables in the XML DB
Register the customer.xsd and account.xsd
schema for the DEMO_CUSTOMERS and DEMO_ACCOUNTS
table respectively, by executing the register_customer script and register_account script in the
XML DB using SQL*Plus.
Step 2: Create the Demo Tables Based on the
XML Schema
Before creating the demo tables, ensure that
the customer and the account schemas are registered in the XML
DB as described in Step 1 above. Then:
1. Create the DEMO_CUSTOMERS
XMLType table based on the customer.xsd schema:
SQL> CREATE TABLE demo_customers OF XMLType XMLSCHEMA "/customer.xsd" ELEMENT "CUSTOMER";
2. Create the DEMO_ACCOUNTS
XMLType table based on the account.xsd schema:
SQL> CREATE TABLE demo_accounts OF XMLType XMLSCHEMA "/account.xsd" ELEMENT "ACCOUNT";
Step 3: Define the Constraints on the XMLType
tables
Constraints can be specified on the existing
tables by using the Alter Table statement.
Constraints can also be specified during the table creation itself
in the Create Table statement. Below we
describe both these ways of specifying the constraints.
1. Listing 1 describes how to define the Unique key constraint on the DEMO_CUSTOMERS
table for the Customer ID element using the Alter Table statement. Listing 2
describes how to create the DEMO_CUSTOMERS table with
in-built Unique key constraint definition.
Listing 2: SQL> CREATE TABLE demo_customers of XMLType (CONSTRAINT CSTID_IS_UNQIUE unique(xmldata."CST_ID")) XMLSCHEMA "/customer.xsd" ELEMENT "CUSTOMER";
2. Listing 3 code snippet
describes how to define the Unique and
the Foreign key constraints on the DEMO_ACCOUNTS
table for Account Number and Customer Id
elements respectively using the Alter Table
statement. Listing 4 describes how to create the DEMO_ACCOUNTS
table with Unique and Foreign key constraint definition in a single
Create Table statement .
Step 4: Insert sample Data into the Tables
and Check the Constraints
Insert sample data in the DEMO_CUSTOMERS
and DEMO_ACCOUNTS table, by executing the insert_customer script and insert_account script in the database
using SQL*Plus. Once these insertions are done we can check the
constraints.
1. Check the Unique key constraint on the
DEMO_CUSTOMERS table:
Try inserting a new record for customer details
in the DEMO_CUSTOMERS table. If the new record has
the same CST_ID as mentioned in the insert_customer script, the following
error will be reported:
ERROR at line 1: ORA-00001: unique constraint (SCOTT.CSTID_IS_UNQIUE) violated
2. Checking the Unique
key constraint on the DEMO_ACCOUNTS table:
Try inserting a new record for account details
in the DEMO_ACCOUNTS table. If the new record has
the same ACC_NO as mentioned in the insert_account script, the following
error will be reported:
ERROR at line 1: ORA-00001: unique constraint (SCOTT.ACCNO_IS_UNQIUE) violated
3. Check the Foreign key constraint on
the DEMO_ACCOUNTS table.
Try inserting a new record for account details
with a CST_ID value for which no customer details
exist in the DEMO_CUSTOMERS table. In this case,
the following error will be reported:
ERROR at line 1: ORA-02291: integrity constraint (SCOTT.ACC_CSTID_IS_VALID) violated - parent key not found
Specifying Constraints between XMLTypeTables and Relational Tables
The following sample scenario describes how
constraints can be specified between XMLType tables and relational tables:
Consider the DEMO_CUSTOMERS
XMLType table (created in the 'Specifying Constraints between XMLType
Tables' section) above and a DEMO_PASSWORD_TABLE
relational table.
The DEMO_CUSTOMERS table contains
all the customer details and the DEMO_PASSWORD_TABLE
contains the password detail of all the customers defined in the DEMO_CUSTOMERS
table. Thus a foreign key needs to be defined between the two.
Next we will look at how to create the relational table, specify constraints
on the table. And check the constraint definition by inserting sample
data into the table.
The DEMO_CUSTOMERS table considered in this scenario
is the same as the one that is described in the ‘Specifying Constraints
between XMLType Tables’ section. Hence it is assumed that this table
is already created and exists in the database schema. If not, please
follow the instructions from Step 1 to Step 4 in the ‘Specifying Constraints between XMLType Tables’
section for creating it and populating the data in it.
Step 1: Create the relational table DEMO_PASSWORD_TABLE
This is the SQL Statement to create the
DEMO_PASSWORD_TABLE:
Step 2: Define Database Constraint on the
DEMO_PASSWORD_TABLE
Constraints can be specified on the existing tables
by using the Alter Table statement. It
can also be specified during the table creation in the Create Table statement. Below we describe both
these ways of specifying the constraints on relational table.
Listing 5 describes how to define
the Foreign key constraint on the
DEMO_PASSWORD_TABLE for Customer ID using the
Alter Table statement. Listing
6 describes how to create the DEMO_PASSWORD_TABLE
table with Foreign key constraint definition
in a single Create Table statement.
Step 3: Insert sample data into the Table
and check the Constraints
Insert sample data into the table DEMO_PASSWORD_TABLE:
SQL> INSERT INTO demo_password_table(customer_id,password) VALUES(1973, 'welcome');
This insert should go through successfully as
the customer record with Customer Id '1973' exists
in the database.
Try inserting a new record in the DEMO_PASSWORD_TABLE
with a CUSTOMER_ID value for which the customer details
does not exist in the DEMO_CUSTOMERS
table. The following error will be reported:
ERROR at line 1: ORA-02291: integrity constraint (SCOTT.CUSTID_IS_VALID) violated - parent key not found
Bookmark
