Sophie: virtuoso-opensource-doc-1:6.1.4-2.fc14 noarch

virtuoso-opensource-doc-6.1.4-2.fc14.noarch.rpm

<!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 ;&#10;" />
  <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="DROPINDEX.html" title="DROP INDEX Statement" />
  <link rel="next" href="CREATEVIEW.html" title="CREATE VIEW 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 ;&#10;" />
  <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="ALTERTABLE" />
    <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="DROPINDEX.html" title="DROP INDEX Statement">Prev</a> | <a class="link" href="CREATEVIEW.html" title="CREATE VIEW 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 &amp; 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>
      <a href="CREATETABLE.html">CREATE TABLE Statement</a>
   </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 class="selected">
      <a href="ALTERTABLE.html">ALTER TABLE Statement</a>
    <div>
        <a href="#sqlrefalttabcheck" title="Adding a CHECK Constraint">Adding a CHECK Constraint</a>
    </div>
   </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 &amp; 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="ALTERTABLE" />
    <h2>8.12. ALTER TABLE Statement</h2>

		<div>
      <pre class="programlisting">
add_column:
            ALTER TABLE q_table_name ADD opt_col_add_column add_col_column_def_list
          | ALTER TABLE q_table_name DROP opt_col_add_column add_col_column_list
          | ALTER TABLE q_table_name MODIFY opt_col_add_column column_def

add_col_column_def_list: column_def
                       | add_col_column_def_list &amp;apos;,&amp;apos; column_def

add_col_column_list: column
                   | add_col_column_list &amp;apos;,&amp;apos; column

table_rename:
            ALTER TABLE q_table_name RENAME new_table_name

alter_constraint:
            ALTER TABLE q_table_name constraint_op opt_table_constraint_def

constraint_op: ADD
             | DROP
             | MODIFY

opt_drop_behavior: /* empty */
                 | CASCADE
                 | RESTRICT

opt_table_constraint_def: CONSTRAINT identifier opt_drop_behavior
                        | table_constraint_def

view_query_spec: query_exp
               | query_no_from_spec

alter_table
	: ALTER TABLE q_table_name ADD opt_col_add_column add_col_column_def_list

	| ALTER TABLE q_table_name DROP opt_col_add_column add_col_column_list

	| ALTER TABLE q_table_name MODIFY opt_col_add_column column_def

	| ALTER TABLE q_table_name RENAME new_table_name

	| ALTER TABLE &lt;table&gt; ADD | DROP | MODIFY &lt;constraint&gt; [&lt;constraint-name&gt;]

&lt;constraint&gt; ::=
	: PRIMARY KEY &#39;(&#39; &lt;column list&gt; &#39;)&#39;
	| FOREIGN KEY &#39;(&#39; &lt;column list&gt; &#39;)&#39; &lt;references&gt;
	| CHECK ( search-condition )

&lt;references&gt; ::=
   REFERENCES &lt;table&gt; [&#39;(&#39; &lt;column list&gt; &#39;)&#39; ] &lt;OPTIONS&gt;

&lt;OPTIONS&gt;  ::=
   [ON UPDATE OPT_ACTION] [ON DELETE OPT_ACTION]
</pre>
    </div>
  <p>The ALTER TABLE statement adds or drops columns and renames tables.</p>
  <p>Adding or dropping a column of a table will create a new version of the
  table&#39;s schema entry.  The rows of the altered table will be changed to
  conform to the new definition when next updated.  All newly inserted rows
  will be in the new row layout.  This means that ALTER TABLE itself executes in
  fixed time without locking the table.  The time to update the actual data
  will be spread over subsequent updates.</p>
  <p>An added column will have a NULL value on those rows where the
  new column has not been set.  A possible default will only apply to newly
  inserted rows.</p>
  <p>When dropping a column one can execute a statement of the form UPDATE
  &lt;table&gt; SET &lt;key&gt; = &lt;key&gt; to force the physical change,
  causing space associated with the dropped column to be freed.</p>
  <p>The column_def in the ADD clause may specify any column options, except
  PRIMARY KEY.</p>
  <p>If the table name is not fully qualified it is completed as in any table
  reference.  The new name in the RENAME clause is defaulted to the current
  qualifier and user account, as in a CREATE TABLE.</p>
  <p>A primary key may only be modified, never dropped or added.  Tables
  always have exactly one primary key.</p>
  <p>The first unique index of an empty table becomes the primary key. This
  may thereafter not be dropped.</p>
  <p>The primary key of an attached table may not be changed.</p>
  <p>Foreign keys can be added.  Dropping a foreign key will drop all
  foreign keys between the foreign key and primary key tables.</p>
  <p>ALTER TABLE cannot be applied to an attached table.</p>
		<a name="ex_altertable" />
    <div class="example">
      <div class="exampletitle">ALTER TABLE Examples</div>
			<div>
        <pre class="programlisting">
ALTER TABLE DISTRICT
    add D_SALES_MGR integer not null references EMPLOYEES (E_ID);

ALTER TABLE idt MODIFY PRIMARY KEY (K1, K2);

ALTER TABLE idt ADD FOREIGN KEY (d) REFERENCES idt (d);

ALTER TABLE idt ADD FOREIGN KEY (k2, k1) REFERENCES idt;

ALTER TABLE idt DROP FOREIGN KEY (d) REFERENCES idt (d);
</pre>
      </div>
		</div>

  <a name="sqlrefalttabcheck" />
    <h3>8.12.1. Adding a CHECK Constraint</h3>

  <p>A CHECK constraint can be added to a table after it has been created
  and populated providing that none of the tables contents would violate the constraint.</p>

  <a name="ex_alttabcheck" />
    <div class="example">
      <div class="exampletitle">Adding a CHECK constraint to an existing table.</div>
<div>
        <pre class="programlisting">
CREATE TABLE test_add_check (
  name VARCHAR,
  age INTEGER
  )
;

ALTER TABLE test_add_check ADD CONSTRAINT chkage CHECK (age &gt; 18);
</pre>
      </div>
  </div>

<div class="tip">
      <div class="tiptitle">See Also:</div>
  <p>
        <a href="CREATETABLE.html#sqlrefcreattablecheck">The CHECK constraint</a>
      </p>
    </div>
  <br />
	<table border="0" width="90%" id="navbarbottom">
    <tr>
        <td align="left" width="33%">
          <a href="DROPINDEX.html" title="DROP INDEX Statement">Previous</a>
          <br />DROP INDEX Statement</td>
     <td align="center" width="34%">
          <a href="sqlreference.html">Chapter Contents</a>
     </td>
        <td align="right" width="33%">
          <a href="CREATEVIEW.html" title="CREATE VIEW Statement">Next</a>
          <br />CREATE VIEW 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>