<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="litsbraceescs.html" title="Literals, Brace Escapes" />
<link rel="next" href="DROPTABLE.html" title="DROP TABLE Statement" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="CREATETABLE" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="litsbraceescs.html" title="Literals, Brace Escapes">Prev</a> | <a class="link" href="DROPTABLE.html" title="DROP TABLE Statement">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div class="selected">
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
<div>
<a href="#syntax" title="Syntax">Syntax</a>
<a href="#consnotnull" title="NOT NULL">NOT NULL</a>
<a href="#identity" title="IDENTITY (Auto Increment)">IDENTITY (Auto Increment)</a>
<a href="#default" title="DEFAULT">DEFAULT</a>
<a href="#pkeycons" title="PRIMARY KEY Constraint">PRIMARY KEY Constraint</a>
<a href="#under" title="UNDER">UNDER</a>
<a href="#fkeys" title="FOREIGN KEY Constraint">FOREIGN KEY Constraint</a>
<a href="#sqlrefcreattablecheck" title="The CHECK Constraint">The CHECK Constraint</a>
<a href="#sqlrefcreattablewithschema" title="The WITH SCHEMA Constraint">The WITH SCHEMA Constraint</a>
</div>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="CREATETABLE" />
<h2>8.8. CREATE TABLE Statement</h2>
<a name="syntax" />
<h3>8.8.1. Syntax</h3>
<div>
<pre class="programlisting">
base_table_def: CREATE TABLE new_table_name '(' base_table_element_commalist ')'
| CREATE TABLE new_table_name as query_exp opt_with_data
base_table_element_commalist: base_table_element
| base_table_element_commalist ',' base_table_element
base_table_element: column_def
| table_constraint_def
column_def: column column_data_type column_def_opt_list
opt_referential_triggered_action: /* empty */
| referential_rule
| referential_rule referential_rule
referential_rule: ON UPDATE referential_action
| delete_referential_rule
delete_referential_rule: ON DELETE_L referential_action
opt_on_delete_referential_rule: /* empty */
| delete_referential_rule
referential_action: CASCADE
| SET NULLX
| SET DEFAULT
references: REFERENCES q_table_name opt_column_commalist opt_referential_triggered_action
column_def_opt_list: /* empty */
| column_def_opt_list column_def_opt
identity_opt: START_L WITH signed_literal
| INCREMENT_L BY INTNUM
identity_opt_list: identity_opt
| identity_opt_list ',' identity_opt
column_def_opt: NOT NULLX
| NULLX
| IDENTITY
| IDENTITY '(' identity_opt_list ')'
| PRIMARY KEY
| DEFAULT signed_literal
| COLLATE q_table_name
| references
| IDENTIFIED BY column
| CHECK '(' search_condition ')'
| WITH SCHEMA column_xml_schema_def
| UNIQUE
table_constraint_def: UNDER q_table_name
| opt_constraint_name PRIMARY KEY '(' index_column_commalist ')' opt_index_option_list
| opt_constraint_name FOREIGN KEY '(' column_commalist ')' references
| opt_constraint_name CHECK '(' search_condition ')'
| opt_constraint_name UNIQUE '(' column_commalist ')'
opt_constraint_name: /* empty */
| CONSTRAINT identifier
column_commalist: column
| column_commalist ',' column
index_column_commalist: column opt_asc_desc
| index_column_commalist ',' column opt_asc_desc
index_option: CLUSTERED
| UNIQUE
| OBJECT_ID
index_option_list: index_option
| index_option_list index_option
opt_index_option_list: /* empty */
| index_option_list
column_xml_schema_def
: '(' STRING ',' STRING ')'
| '(' STRING ',' STRING ',' STRING ')'
opt_with_data
: /* empty */
| WITH DATA
| WITHOUT DATA
</pre>
</div>
<p>The CREATE TABLE statement creates a new table. The table is identified by a
new_table_name, which consists of an optional qualifier, an optional owner and
the name. If the qualifier is not supplied then it defaults to the current qualifier,
and likewise if the table owner is not specified then this will default to the login
name of the user issuing the statement.</p>
<p>
Following the new_table_name is a list of table elements that are either column
definitions or table constraints. A column must have a unique name within the table
and possible super tables. The only necessary attribute of a column is a data type.
</p>
<p>The UNDER q_table_name constraint specifies an optional supertable. The
table being created will inherit all columns, indices, constraints from the
supertable, specifically including the primary key. Hence the under and primary
key constraints cannot be specified together. If the table being created will be
owned by a non-dba user, the supertable must be owned by the same user.</p>
<p>The AS query_exp opt_with_data causes the table to be created based on the
compile time types of columns of the SELECT expression query_exp. If WITH DATA
is specified then the resultset returned by the query_exp is fed into the new
table. Otherwise (if WITHOUT DATA or not specified) only the table is created.</p>
<br />
<a name="consnotnull" />
<h3>8.8.2. NOT NULL</h3>
<p>Optionally a column can be declared NOT NULL. Any attempts to
insert NULL into column declared NOT NULL will result in an error and the
insert rejected.</p>
<br />
<a name="identity" />
<h3>8.8.3. IDENTITY (Auto Increment)</h3>
<p>The IDENTITY keyword causes the columns to be automatically incremental,
meaning that each successive insert into the table will automatically assign
a value greater than any previous row of the table. These columns are
often referred to as "autoincrement columns". The assigned values
are normally consecutive integers. There may be gaps in the sequence if some
transactions that reserve a number fail to commit. </p>
<p>An autoincrement column may or may not be part of the primary key or
of any index.</p>
<p>The <a href="fn_identity_value.html">identity_value()</a>
function returns the identity column value most recently assigned on the current connection. This can be used inside procedures or from clients. See also the ODBC statement option SQL_GETLASTSERIAL.</p>
<p>The <a href="fn_set_identity_column.html">set_identity_column()</a>
function allows you to change the identity column sequence value.</p>
<p>The IDENTITY keyword supports the SQL 3 optional clause that allows
you to specify a starting value and/or step value in the table creation statement. The syntax is:</p>
<div>
<pre class="programlisting">
IDENTITY (START WITH <N>, INCREMENT BY <Y>)
</pre>
</div>
<a name="ex_identity" />
<div class="example">
<div class="exampletitle">Using the IDENTITY declaration</div>
<div>
<pre class="programlisting">
CREATE TABLE DB.DBA.AI (
AI INTEGER IDENTITY,
XX VARCHAR,
PRIMARY KEY (AI)
)
;</pre>
</div>
<p>Creates a table with an autoincrement primary key column AI.</p>
<div>
<pre class="programlisting">
set_identity_column("DB"."DBA"."AI", "AI", 11);</pre>
</div>
<p>will cause the next row to be inserted into AI to have the AI column
equal to 11.</p>
</div>
<a name="ex_identity" />
<div class="example">
<div class="exampletitle">Using the IDENTITY (START WITH) declaration</div>
<div>
<pre class="programlisting">
CREATE TABLE DB.DBA.AI (
AI INTEGER IDENTITY (START WITH 100),
XX VARCHAR,
PRIMARY KEY (AI)
)
;</pre>
</div>
<p>Creates a table with an autoincrement primary key column AI, whose
first value will be 100 and will auto-increment from there upwards.</p>
</div>
<div class="tip">
<div class="tiptitle">See Also</div>
<p>
<a href="sequenceobjects.html">Sequence Objects</a>
</p>
</div>
<br />
<a name="default" />
<h3>8.8.4. DEFAULT</h3>
<p>
This option specifies a constant default value given to the column by an INSERT
that does not specify a value for the column. The constant must be compatible
with the column's data type. An IDENTITY or TIMESTAMP column cannot have
a default value.
</p>
<br />
<a name="pkeycons" />
<h3>8.8.5. PRIMARY KEY Constraint</h3>
<p>This declares a column combination that will uniquely identify each
row in the table. This automatically defines an index on these columns
and clusters the physical rows of the table together with the index entry
for this primary key index. Always specify a primary key if there is a
uniquely identifying column combination on the table. This is the case for
any normalized database. Hence virtually all tables should have the primary
key constraint. This is substantially more efficient than defining the
primary key as a unique index with CREATE INDEX. The primary key constraint
exists for the purpose of guaranteeing uniqueness of a row and hence should
be respected. A unique index is not a primary key and should never
substitute one.</p>
<a name="ex_pkeyfullconstraint" />
<div class="example">
<div class="exampletitle">Primary Key using Constraint</div>
<p>This example shows how to create a table with a primary key defined in
full as a named primary key table constraint</p>
<div>
<pre class="programlisting">
CREATE TABLE demo_table (
id INTEGER NOT NULL,
txtdata VARCHAR(20),
CONSTRAINT demo_table_pk PRIMARY KEY (id)
)
;</pre>
</div>
</div>
<p>PRIMARY KEY is a shorthand for the PRIMARY KEY (column)
table constraint which is specified in the column definition. SQL-89 required
that you specify NOT NULL PRIMARY KEY but SQL-92 does not because primary
keys columns do not permit NULL values. This means that no members of a
combination of columns that constitute a primary key can have a NULL value.</p>
<a name="ex_pkeyshort" />
<div class="example">
<div class="exampletitle">Primary Key shorthand</div>
<p>This example shows how to create a table with a primary key defined
using shorthand:</p>
<div>
<pre class="programlisting">
CREATE TABLE demo_table (
id INTEGER NOT NULL PRIMARY KEY,
txtdata VARCHAR(20),
)
;</pre>
</div>
<p>Or shorter still:</p>
<div>
<pre class="programlisting">
CREATE TABLE demo_table (
id INTEGER PRIMARY KEY,
txtdata VARCHAR(20),
)
;</pre>
</div>
</div>
<p>If a table definition has no PRIMARY KEY clause, Virtuoso will generate
a default primary key column called "_IDN". <span class="important">
<strong>Important:</strong> Always declare a primary key using
the primary key table constraint. This is always more efficient than the
default primary key.</span> You could allow Virtuoso to generate the
primary key if you simply want an IDENTITY primary key column, however this
is considered lazy and, of course, not portable. If you already have a
column or combination of columns that could be a candidate for a primary
key, taking the default primary key from Virtuoso will reduce the overall
efficiency because an extra column will be used per row that would be
redundant to the data itself.</p>
<a name="ex_pkeydefault" />
<div class="example">
<div class="exampletitle">Default Virtuoso Primary Key</div>
<p>In the absence of a PRIMARY KEY definition:</p>
<div>
<pre class="programlisting">
CREATE TABLE SAMPLE (
THING VARCHAR
)
;</pre>
</div>
<p>will be expanded into:</p>
<div>
<pre class="programlisting">
CREATE TABLE SAMPLE (
THING VARCHAR,
_IDN INTEGER IDENTITY,
PRIMARY KEY (_IDN)
)
;</pre>
</div>
</div>
<p>Tables with generated default primary keys will appear as if they have
no primary key defined. The default primary key (_IDN) column will not appear
in the ODBC catalog calls <span class="computeroutput">SQLColumns()</span>,
<span class="computeroutput">SQLStatistics()</span>, <span class="computeroutput">SQLPrimaryKeys()</span>,
<span class="computeroutput">SQLColumnPrivileges()</span>. The column can be explicitly
referenced in SQL statements just as any other. The SQL "SELECT * ..."
statement will omit the _IDN column. The "INSERT INTO TABLE VALUES (.)"
statement does not expect a value for the _IDN column.
<span class="computeroutput">SQLSpecialColumns()</span> with the <span class="computeroutput">SQL_BEST_ROWID</span>
option is the only catalog call that will return the _IDN column.</p>
<p>The PRIMARY KEY option may not coexist with UNDER in a CREATE TABLE
statement because a subtable always inherits the supertable's primary key.</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="CREATEINDEX.html">CREATE INDEX</a> for the index options.</p>
</div>
<br />
<a name="under" />
<h3>8.8.6. UNDER</h3>
<p>This allows the user to create a table as a sub-table of an existing table.
A sub-table will inherit all columns and constraints of its super-table, most
importantly the primary key. Primary keys shall be unique in the set of rows
belonging to any direct or indirect sub-table of one super-table. A select
from a super-table will see all rows, including those of any sub-tables, but
not vice versa. Rows inserted directly into the super-table will not be
visible to sub-tables.</p>
<p>
The primary key option may not coexist with UNDER, since a subtable always
inherits the supertable's primary key.
</p>
<a name="ex_under" />
<div class="example">
<div class="exampletitle">Creating sub-tables using UNDER</div>
<p>Here a subtable will be created for the demo_table defined earlier.
Both definitions are listed for convenience. Notice that the select over
the sub-table (demo_sub_table) lists all columns whereas the select from
super-table does not, however rows inserted into the super-table,
demo_table, will not be seen in a select from the sub-table, but rows
inserted into the sub-table will be shown in a select from the super-table.</p>
<div>
<pre class="programlisting">
CREATE TABLE demo_table (
id INTEGER PRIMARY KEY,
txtdata VARCHAR(20),
)
;
CREATE TABLE demo_sub_table (
new_col VARCHAR(10),
UNDER demo_table
)
;
SQL> select * from demo_table;
id txtdata
INTEGER NOT NULL VARCHAR
____________________________________________________________________
0 Rows. -- 20 msec.
SQL> select * from demo_sub_table;
id txtdata new_col
INTEGER NOT NULL VARCHAR VARCHAR
____________________________________________________________________
0 Rows. -- 10 msec.
</pre>
</div>
</div>
<br />
<a name="fkeys" />
<h3>8.8.7. FOREIGN KEY Constraint</h3>
<p>A FOREIGN KEY is a column of combination of columns used to retrieve a related
row from a related table. These reference constraints are automatically enforced,
unless explicitly disabled. This declaration can be accessed by client
applications using the <span class="computeroutput">SQLForeignKeys()</span> ODBC
function. This is used by some tools to display dependencies between
tables. Foreign keys are there to maintain referential integrity within
the database. This constraint will ensure that rows in one table will
have corresponding rows in another table, so that your orders are not
made for products that do not exist, for example.</p>
<p>Forward references are not permitted in FOREIGN KEY. Also a table
referenced in a foreign key constraint of another table cannot be dropped.
It is usual to list the columns in the referenced table corresponding
to the columns in the referencing table, however, this list can be omitted,
in which case the set of primary key columns of the referenced table is
used automatically by default. The referenced and referencing column
lists must be of equal length. To declare two mutually referencing tables,
ALTER TABLE must be used after both tables are defined. </p>
<p>Foreign key constraints are enforced by default. It is sometimes
practical to disable constraint checks for performance or for convenience
when importing data or making bulk updates. To disable or enable checking
for foreign key constraints you can use the
<a href="fn_fk_check_input_values.html">fk_check_input_values()</a>
function. This function changes the foreign key checking behavior globally
across the database so it is very important to remember to enable foreign key
constraint checking again afterwards.</p>
<p>Columns can be defined as having foreign keys and a default value,
however, the default value must not violate the constraint at the time of
insert or update as this will be rejected in the normal way.</p>
<a name="ex_fkeysinuse" />
<div class="example">
<div class="exampletitle">Creating tables with foreign key constraints</div>
<p>First the Primary key table must be defined before it can be referenced:</p>
<div>
<pre class="programlisting">
CREATE TABLE T1 (
Id INTEGER NOT NULL PRIMARY KEY,
Dt VARCHAR
)
;</pre>
</div>
<p>Now the foreign key table can be created referencing the table above:</p>
<div>
<pre class="programlisting">
CREATE TABLE T2 (
Act INTEGER NOT NULL, -- will refer to Id in T1
Retr DATETIME,
Description VARCHAR,
CONSTRAINT FK1 FOREIGN KEY (Act) REFERENCES T1 (Id) -- the fk constraint
)
;</pre>
</div>
<p>The statement above creates the foreign key constraint in separate line
of the create table statement. This can be also be written in short form
with the column definition it applies to:</p>
<div>
<pre class="programlisting">
CREATE TABLE T2 (
Act INTEGER NOT NULL REFERENCES T1 (Id), -- refer to Id in T1
Retr DATETIME,
Description VARCHAR
)
;</pre>
</div>
</div>
<a name="ex_nwdemofk" />
<div class="example">
<div class="exampletitle">Assumed Foreign Key Columns</div>
<p>This CREATE TABLE statement was used for creating part of Demo database.
This statement does not describe the columns to be used as foreign keys on
the referred tables. Since the referred tables in each case have a single
Primary Key (Orders.OrderID and Products.ProductID) they need not be
mentioned and will be assumed when this statement is processed.</p>
<div>
<pre class="programlisting">
CREATE TABLE Order_Details(
OrderID INTEGER,
ProductID INTEGER,
UnitPrice DOUBLE PRECISION,
Quantity SMALLINT,
Discount REAL,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders,
FOREIGN KEY (ProductID) REFERENCES Products
)
;</pre>
</div>
</div>
<a name="fkeyactions" />
<h4>8.8.7.1. Foreign Key Constraint Actions</h4>
<p>Foreign key constraint actions allow the user to define what action
data should take when the data they are related to by way of a foreign key
is updated or deleted. The two activities that can be programmed are:</p>
<ul>
<li>ON DELETE</li>
<li>ON UPDATE</li>
</ul>
<p>The actions available for both types of activity are:</p>
<ul>
<li>CASCADE - automatically perform the same action on the referenced data.</li>
<li>SET NULL - set the referenced data to NULL.</li>
<li>SET DEFAULT - sets the referenced data to whatever was defined as its default value.</li>
<li>NO ACTION - to not perform any action at all.</li>
</ul>
<a name="fkeyactions" />
<div class="example">
<div class="exampletitle">Foreign Key Constraint Actions</div>
<p>Now, the foreign key table defined again, but this time with referential
rules which control how data is managed when rows are updated or deleted in
the primary key table:</p>
<div>
<pre class="programlisting">
CREATE TABLE T3 (
Act INTEGER NOT NULL DEFAULT 99, -- will refer to Id in T1
Retr DATETIME,
Description VARCHAR,
CONSTRAINT Fk1 FOREIGN KEY (Act) REFERENCES T1 (Id)
ON UPDATE CASCADE ON DELETE SET DEFAULT
)
;</pre>
</div>
</div>
<br />
<br />
<a name="sqlrefcreattablecheck" />
<h3>8.8.8. The CHECK Constraint</h3>
<p>The CHECK constraint allows you specify and wide range of rules that
will dictate whether an insert of update will be permitted. The syntax is as follows:</p>
<div>
<pre class="programlisting">
CHECK (search-condition)
</pre>
</div>
<p>The search condition can be simple and comparative, or quite complicated
involving regular expressions.</p>
<a name="ex_sqlrefcreatecheck" />
<div class="example">
<div class="exampletitle">Creating a table with the CHECK constraint</div>
<p>Here a simple table will be created with two CHECK constraints. One the
check constraints is a simple comparison ensuring participants are over 18, the
other complicated constraint verifies that the email address is correct using a
regular expression. Samples SQL statements follow that will demonstrate the
effectiveness of the check constraints.</p>
<div>
<pre class="programlisting">
CREATE TABLE test_check (
name VARCHAR,
age INTEGER
CHECK (age > 18),
email VARCHAR
CHECK (regexp_like(email, '^([a-zA-Z0-9_.-])+@(([a-zA-Z0-9-])+.)+([a-zA-Z0-9]{2,4})+\$'))
)
;
INSERT INTO test_check (name, age, email) VALUES ('Jack', 18, 'jack@foo.bar');
-- will cause: *** Error 22023: ... SR363: CHECK constraint violated
INSERT INTO test_check (name, age, email) VALUES ('Jill', 19, 'up@thehill.com');
-- will be insert correctly.
INSERT INTO test_check (name, age, email) VALUES ('Jack and Jill', 37, 'ouch/!^^!!@@');
-- will cause: *** Error 22023: ... SR363: CHECK constraint violated, also.
</pre>
</div>
</div>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="fn_regexp_like.html">regexp_like()</a>
</p>
</div>
<br />
<a name="sqlrefcreattablewithschema" />
<h3>8.8.9. The WITH SCHEMA Constraint</h3>
<p>The WITH SCHEMA constraint allows you force values of an XML column to match a
particular schema. The syntax is as follows:</p>
<div>
<pre class="programlisting">
WITH SCHEMA (namespace-uri, top-element-name [, optional-dtd-configuration])
</pre>
</div>
<p>To use this feature, you should make load an XMLSchema to the server by a
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA</a> statement.
As soon as schema is loaded, its target namespace URI can be used in WITH SCHEMA
constraint to validate every new value of the column against this schema.
In addition, the constraint ensures that the document is a well-formed XML document
(not a well-formed generic XML entity) and checks if the name of the top level element
of the document is equal to one specified in the constraint.
</p>
<p>Depending on document size
and the complexity of the schema, schema validation may be a time- and memory- consuming
operation. An application can win a lot if every stored value is validated only once
and avoid repeating validations on every read of the stored data, but it also may loose
if the validation is actually redundant (e.g. if the data always comes from sources which produce valid content).
</p>
<p>
The parameter "optional-dtd-configuration" lets an application to specify
how strict the validation should be. In real life, documents may match the schema in general,
but not in minor details that are not important for a particular application.
If specified, the parameter must be a string in the format described in
<a href="xmlschema.html#dtd_config">Configuration Options of the DTD Validator</a>.
The default value is suitable for most of applications and forces the validator to
check well-formedness of the document, nesting of elements, presence of
all "required" attributes, syntax of values of typed elements and attributes;
it also will check referential integrity of ID and IDREF attributes.
</p>
<a name="ex_sqlrefcreattablewithschema" />
<div class="example">
<div class="exampletitle">Creating a table with the WITH SCHEMA constraint</div>
<p>Here a simple table will be created with a WITH SCHEMA constraint.
Sample SQL statements follow that will demonstrate the
effectiveness of the check constraints.</p>
<div>
<pre class="programlisting">
CREATE XML SCHEMA '<?xml version="1.0" encoding="utf-8" ?>
<xs:schema targetNamespace="http://www.example.com/parts"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:p="http://www.example.com/parts">
<xs:element name="Part">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element ref="p:Part"/>
</xs:choice>
<xs:attribute name="ID" type="xs:string" use="required" />
<xs:attribute name="Count" type="xs:int" use="optional" />
<xs:attribute name="Type" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>';
create table SPARE_PARTS (
PACK_ID integer primary key,
CONTENT XMLType with schema ('http://www.example.com/parts', 'Part')
);
insert into SPARE_PARTS values (1, '
<p:Part xmlns:p="http://www.example.com/parts" ID="keyboard">
<p:Part Count="101"/>
<p:Part ID="body"/>
</p:Part>')
*** Error 42000: [Virtuoso Driver][Virtuoso Server]XML parser detected an error:
ERROR : Only 0 out of 1 required attributes are defined for element <p:Part>, e.g. the element has no attribute 'ID'
at line 3 column 25 of '(value to be placed in column DB.DBA.SPARE_PARTS of CONTENT)'
<p:Part Count="101"/>
-----------------------^
insert into SPARE_PARTS values (2, '
<p:Part xmlns:p="http://www.example.com/parts" ID="keyboard">
<p:Part ID="key" Count="101"/>
<p:Part ID="body"/>
</p:Part>')
select * from SPARE_PARTS
PACK_ID CONTENT
INTEGER NOT NULL LONG VARCHAR
_______________________________________________________________________________
2
<n0:Part xmlns:n0="http://www.example.com/parts" ID="keyboard">
<n0:Part ID="key" Count="101" />
<n0:Part ID="body" />
</n0:Part>
1 Rows. -- 00000 msec.
</pre>
</div>
</div>
<p>ALTER TABLE ... MODIFY COLUMN statement does not support changes of WITH SCHEMA constraint.
Double check your XMLSchema and carefully test it on real data used by an application before
using this constraint. If you can't test your schema this way then calling of
<a href="fn_xml_validate_schema.html">xml_validate_schema()</a>
in triggers may be safer than using the constraint: such triggers will be slower than
the constraint but you can drop triggers without re-creating the table.
</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</p>
<p>
<a href="fn_xml_validate_schema.html">xml_validate_schema()</a>
</p>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="litsbraceescs.html" title="Literals, Brace Escapes">Previous</a>
<br />Literals, Brace Escapes</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="DROPTABLE.html" title="DROP TABLE Statement">Next</a>
<br />DROP TABLE Statement</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
26
