SQL DDL syntax: Difference between revisions

From m204wiki
Jump to navigation Jump to search
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
__NOTOC__
 
==Overview==
==Overview==
This topic outlines in complete detail the syntax of Model 204 SQL DDL. Consult the American National Standards ANSI X3.135-1989 Database Language SQL document for syntax details not outlined here. The notation conventions used in this topic are listed in [[Notation conventions]].
This topic outlines in complete detail the syntax of Model 204 SQL DDL. Consult the American National Standards ANSI X3.135-1989 Database Language SQL document for syntax details not outlined here. The notation conventions used in this topic are listed in [[Notation conventions]].
Line 6: Line 6:
<ol>
<ol>
<li>
<li>
SQL object names (that is, schema name, table name, and so on) conform to rules for an SQL identifier: a name must be 18 characters or less and it cannot be identical to an SQL keyword (the list of keywords is detailed in the standard). Model 204 SQL Reserved Words are listed in [[SQL reserved words]].</li>
SQL object names (that is, schema name, table name, and so on) conform to rules for an SQL identifier: <br />a name must be 18 characters or fewer, and it cannot be identical to an SQL keyword (the list of keywords is detailed in the standard). Model 204 SQL Reserved Words are listed in [[SQL reserved words]].</li>
<li>
<li>
End of line (newline) is implementation defined.
End of line (newline) is implementation defined.
Line 13: Line 13:
</li>
</li>
<li>
<li>
Numbers in parentheses to the right of some of the syntax lines are keys to notes that appear at the end of the syntax diagrams.</li>
Numbers in square brackets to the right of some of the syntax lines are keys to notes that appear at the end of the syntax diagrams.</li>
<li>
<li>
Model 204 SQL DDL extensions to the standard are printed in <b><i>bold italics</i></b>; statements or clauses that are not part of the ANSI SQL 1989 standard or the ANSI SQL 1992 standard, but that are anticipated to be part of the emerging standard are printed in <i>italics</i>.</li>
Model 204 SQL DDL extensions to the standard are printed in <b><i>bold italics</i></b>; statements or clauses that are not part of the ANSI SQL 1989 standard or the ANSI SQL 1992 standard, but that are anticipated to be part of the emerging standard are printed in <i>italics</i>.</li>
Line 20: Line 20:
==DDL syntax==
==DDL syntax==
<p class="syntax"><span class="term">tablename</span> ::=
<p class="syntax"><span class="term">tablename</span> ::=
   [<span class="term">schemaname</span>.] &lt;<span class="term">table</span>&gt;
   [<span class="term">schemaname</span>.] &lt;<span class="term">table</span>&gt;</p>


table ::=
<p class="syntax">table ::=
   basetable | viewname
   basetable | viewname</p>


schema ::=
<p class="syntax">schema ::=
  CREATE SCHEMA <schema-authorization-clause>
  CREATE SCHEMA <schema-authorization-clause>
   [ <span class="term">schema-element</span> ... ]
   [ <span class="term">schema-element</span> ... ]</p>


schema-authorization-clause ::=  
<div id="one"></div>
   <span class="term">schemaname</span></p>
<p class="syntax">schema-authorization-clause ::=  
   <span class="term">schemaname</span>
| AUTHORIZATION authorization-id[[#note one|<sup><b>[1]</b></sup>]]
| <span class="term">schemaname</span> AUTHORIZATION authorization-id[[#note one|<sup><b>[1]</b></sup>]]</p>


===Section 1===
<p class="syntax">schema-element ::=
<p class="syntax"> | AUTHORIZATION authorization-id</p>
 
<b>1</b>
<p class="syntax"> | <span class="term">schemaname</span> AUTHORIZATION authorization-id
schema-element ::=
   <table-definition>
   <table-definition>
   | <view-definition>
   | <view-definition>
   | <privilege-definition>
   | <privilege-definition></p>


table-definition ::=
<p class="syntax">table-definition ::=
   CREATE TABLE <tablename>
   CREATE TABLE <tablename>
  <b>[ <<span class="term">file-mapping-clause</span>> | NESTED USING <<span class="term">nested-key</span>> ]</b>
  <b>[ <<span class="term">file-mapping-clause</span>> | NESTED USING <<span class="term">nested-key</span>> ]</b>
   ( <table-element> ,... )
   ( <table-element> ,... )</p>
    
    
<span class="term">file-mapping-clause</span> ::=
<p class="syntax"><span class="term">file-mapping-clause</span> ::=
   <span class="term">SYSNAME</span> '<span class="term">filename</span>'
   <span class="term">SYSNAME</span> '<span class="term">filename</span>'</p>
    
    
<span class="term">nested-key</span> ::=
<p class="syntax"><span class="term">nested-key</span> ::=
<span class="term">columname</span>
<span class="term">columname</span></p>


table-element ::=
<p class="syntax">table-element ::=
   <column-definition>
   <column-definition>
   | <table-constraint-definition></p>
   | <table-constraint-definition></p>


===Section 2===
<div id="two"></div>
<p class="syntax">column-definition ::=
<p class="syntax">column-definition ::=[[#note two|<sup><b>[2]</b></sup>]]
columname <datatype>
columname <datatype>
   <b>[ <<span class="term">field-mapping-clause</span>> ]</b>
   <b>[ <<span class="term">field-mapping-clause</span>> ]</b>
   [ <column-constraint> ... ]
   [ <column-constraint> ... ]</p>


datatype ::=</p>
<div id="three"></div>
 
<div id="four"></div>
===Section 3===
<div id="five"></div>
<p class="syntax"> CHAR[ACTER] [(length)]</p>
<p class="syntax">datatype ::=
 
  CHAR[ACTER] [(length)][[#note three|<sup><b>[3]</b></sup>]]
===Section 4===
  | NUM[ERIC] [(precision [,scale])][[#note four|<sup><b>[4]</b></sup>]]
<p class="syntax">   | NUM[ERIC] [(precision [,scale])]
   | DEC[IMAL] [(precision [,scale])]
   | DEC[IMAL] [(precision [,scale])]
   | INT[EGER]
   | INT[EGER]
   | SMALLINT</p>
   | SMALLINT
 
  | FLOAT [(precision)][[#note five|<sup><b>[5]</b></sup>]]
===Section 5===
<p class="syntax">  | FLOAT [(precision)]
   | REAL
   | REAL
   | DOUBLE PRECISION
   | DOUBLE PRECISION
   | BLOB
   | BLOB
   | CLOB
   | CLOB</p>


<span class="term">field-mapping-clause</span>:=
<p class="syntax"><span class="term">field-mapping-clause</span> ::=
  <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' </p>
  <span class="term">SYSNAME</span> '<span class="term">fieldname</span>'</p>


===Section 6===
<div id="six"></div>
<p class="syntax">column-constraint ::=
<div id="seven"></div>
   NOT NULL</p>
<p class="syntax">column-constraint ::=[[#note six|<sup><b>[6]</b></sup>]]
   NOT NULL
  | <unique-specification>[[#note seven|<sup><b>[7]</b></sup>]]
  | <references-specification></p>


===Section 7===
<p class="syntax">table-constraint-definition ::=
<p class="syntax"> | <unique-specification>
  | <references-specification>
 
table-constraint-definition ::=
   <unique-constraint-definition>
   <unique-constraint-definition>
  | <referential-constraint-definition>
  | <referential-constraint-definition></p>
 
unique-constraint-definition ::=
  <unique-specification> (<column-list>)</p>


===Section 8===
<div id="eight"></div>
<p class="syntax"> [ <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' ]
<p class="syntax">unique-constraint-definition ::=
  <unique-specification> (<column-list>)
  [ <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' ][[#note eight|<sup><b>[8]</b></sup>]]</p>


unique-specification ::=
<p class="syntax">unique-specification ::=
   UNIQUE | PRIMARY KEY [<span class="term"><b>SYSTEM</b></span>]</p>
   UNIQUE | PRIMARY KEY [<span class="term"><b>SYSTEM</b></span>]</p>


===Section 9===
<div id="nine"></div>
<p class="syntax">referential-constraint-definition ::=
<p class="syntax">referential-constraint-definition ::=[[#note nine|<sup><b>[9]</b></sup>]]
   FOREIGN KEY (columname)
   FOREIGN KEY (columname)
   <references-specification>  
   <references-specification></p>
 
references-specification ::=</p>


<b>9</b>
<p class="syntax">references-specification ::=
<p class="syntax"> REFERENCES <b>parent-table-name</b>
  REFERENCES <b>parent-table-name</b>[[#note nine|<sup><b>[9]</b></sup>]]
   [<referential-triggered-action>]
   [<referential-triggered-action>]</p>


referential-triggered-action ::=
<p class="syntax">referential-triggered-action ::=
     <update-rule> [ <delete-rule> ]
     <update-rule> [ <delete-rule> ]
   | <delete-rule> [ <update-rule> ]  
   | <delete-rule> [ <update-rule> ]</p>
 
update-rule ::= ON UPDATE CASCADE
 
delete-rule ::= ON DELETE CASCADE


view-definition::=
<p class="syntax">update-rule ::= ON UPDATE CASCADE</p>  
  CREATE VIEW <viewname> [(<column-list>)]</p>


===Section 10===
<p class="syntax">delete-rule ::= ON DELETE CASCADE</p>
<p class="syntax">   AS <<span class="term"><b>query-expression</b></span>> [WITH CHECK OPTION]


set user statement::=</p>
<div id="ten"></div>
<p class="syntax">view-definition ::=
  CREATE VIEW <viewname> [(<column-list>)]
    AS <<span class="term"><b>query-expression</b></span>> [WITH CHECK OPTION][[#note ten|<sup><b>[10]</b></sup>]]</p>


===Section 11===
<div id="eleven"></div>
<p class="syntax"> SET USER authorization-id
<p class="syntax">set user statement ::=
  SET USER authorization-id[[#note eleven|<sup><b>[11]</b></sup>]]</p>


set schema statement::=
<p class="syntax">set schema statement ::=
   <span class="term">SET SCHEMA schemaname</span>  
   <span class="term">SET SCHEMA schemaname</span></p>  


drop schema statement::=</p>
<div id="twelve"></div>
<p class="syntax">drop schema statement ::=
  DROP SCHEMA schemaname[[#note twelve|<sup><b>[12]</b></sup>]]</p>


===Section 12===
<p class="syntax">drop table statement ::=
<p class="syntax"> DROP SCHEMA schemaname
  DROP TABLE <tablename>[[#note twelve|<sup><b>[12]</b></sup>]]</p>


drop table statement::=</p>
<p class="syntax">drop view statement ::=
  DROP VIEW <viewname>[[#note twelve|<sup><b>[12]</b></sup>]]</p>


<b>12</b>
<p class="syntax">alter table statement ::=
  ALTER TABLE <tablename> <alter-table-action></p>


<p class="syntax"> DROP TABLE <tablename>
<div id="thirteen"></div>
 
<p class="syntax">alter-table-action ::=
drop view statement::=</p>
   ADD <column-definition>[[#note thirteen|<sup><b>[13]</b></sup>]]
 
<b>12</b>
<p class="syntax"> DROP VIEW <viewname>
 
alter table statement ::=
   ALTER TABLE <tablename> <alter-table-action>
 
alter-table-action::=</p>
 
===Section 13===
<p class="syntax"> ADD <column-definition>
   | DROP columname
   | DROP columname
   | MODIFY <column-parameters>
   | MODIFY <column-parameters></p>
 
column-parameters::=</p>


===Section 14===
<div id="fourteen"></div>
<p class="syntax"> MODIFY columname
<p class="syntax">column-parameters ::=
  MODIFY columname[[#note fourteen|<sup><b>[14]</b></sup>]]
   [<datatype>]  
   [<datatype>]  
   [<<b>field-mapping-clause</b>>]
   [<<b>field-mapping-clause</b>>]
   [<modify-attribute-list> ...]
   [<modify-attribute-list> ...]</p>


modify-attribute-list ::=
<p class="syntax">modify-attribute-list ::=
   [ NOT ] NULL  
   [ NOT ] NULL  
   | [ NOT ] UNIQUE
   | [ NOT ] UNIQUE</p>


privilege-definition ::=
<p class="syntax">privilege-definition ::=
  GRANT <privileges> ON <object-name>
  GRANT <privileges> ON <object-name>
  TO <grantee> ,...
  TO <grantee> ,...
  [ WITH GRANT OPTION ]  
  [ WITH GRANT OPTION ]</p>


privileges ::=
<p class="syntax">privileges ::=
  ALL PRIVILEGES
  ALL PRIVILEGES
  | <action> ,...
  | <action> ,...</p>


action::=
<div id="fifteen"></div>
<p class="syntax">action ::=
   SELECT
   SELECT
   | INSERT
   | INSERT
   | DELETE</p>
   | DELETE
 
  | UPDATE [(<column-list>)][[#note fifteen|<sup><b>[15]</b></sup>]]</p>
===Section 15===
<p class="syntax">  | UPDATE [(<column-list>)]


column list::=
<p class="syntax">column list ::=
   columname,...  
   columname,...</p>


object-name::=
<p class="syntax">object-name ::=
   <tablename> | <viewname>  
   <tablename> | <viewname></p>


grantee::=
<p class="syntax">grantee ::=
   PUBLIC
   PUBLIC
   | authorization-id  
   | authorization-id</p>


revoke statement ::=
<p class="syntax">revoke statement ::=
  REVOKE [ GRANT OPTION FOR ] <privileges>
  REVOKE [ GRANT OPTION FOR ] <privileges>
  ON <object-name>
  ON <object-name>
Line 210: Line 190:
==Notes for syntax display==
==Notes for syntax display==


The numbers for the comments below correspond (and link) to the section numbers in the preceding syntax listing.
The numbers for the comments below correspond (and link) to the footnote numbers in the preceding syntax listing.
<table class="noBorder">
<table class="noBorder">
<tr>
<tr><div id="note one"></div>
<td nowrap><b>[[#Section 1|Section 1]]</b></td> <td>Since the AUTHORIZATION value maps to a Model 204 user ID, it can be no longer than 10 characters.</td>
<td>[[#one|<b>1</b>]]</td> <td>Since the AUTHORIZATION value maps to a Model 204 user ID, it can be no longer than 10 characters.</td></tr>
</tr>
 
<tr>
<td><b>[[#Section 2|Section 2]]</b></td> <td>The column definition DEFAULT clause is not supported in Model 204 SQL.</td>
</tr>


<tr>
<tr><div id="note two"></div>
<td><b>[[#Section 3|Section 3]]</b></td> <td>Referred to as <var class="term">character string type</var> in the standard. The default length is 1.</td>
<td>[[#two|<b>2</b>]]</td> <td>The column definition DEFAULT clause is not supported in Model 204 SQL.</td></tr>
</tr>


<tr>
<tr><div id="note three"></div>
<td><b>[[#Section 4|Section 4]]</b></td> <td>NUMERIC, DECIMAL, INTEGER, and SMALLINT are referred to as <var class="term">exact numeric</var> types in the standard. The precision values are interpreted as decimal precision.</td>
<td>[[#three|<b>3</b>]]</td> <td>Referred to as <var class="term">character string type</var> in the standard. The default length is 1.</td></tr>
</tr>


<tr>
<tr><div id="note four"></div>
<td><b>[[#Section 5|Section 5]]</b></td> <td>FLOAT, REAL, and DOUBLE PRECISION are referred to as <var class="term">approximate numeric</var> types in the standard. The precision value is interpreted as binary precision.</td>
<td>[[#four|<b>4</b>]]</td> <td>NUMERIC, DECIMAL, INTEGER, and SMALLINT are referred to as <var class="term">exact numeric</var> types in the standard. The precision values are interpreted as decimal precision.</td></tr>
</tr>


<tr>
<tr><div id="note five"></div>
<td><b>[[#Section 6|Section 6]]</b></td> <td>CHECK column constraint is not supported in Model 204 SQL.</td>
<td>[[#five|<b>5</b>]]</td> <td>FLOAT, REAL, and DOUBLE PRECISION are referred to as <var class="term">approximate numeric</var> types in the standard. The precision value is interpreted as binary precision.</td></tr>
</tr>


<tr>
<tr><div id="note six"></div>
<td><b>[[#Section 7|Section 7]]</b></td> <td>Unlike the standard, UNIQUE is independent of NOT NULL. If you specify UNIQUE, NOT NULL is not implied.
<td>[[#six|<b>6</b>]]</td> <td>CHECK column constraint is not supported in Model 204 SQL.</td></tr>
<p>In addition, PRIMARY KEY is syntactically independent of NOT NULL. Specifying PRIMARY KEY without NOT NULL is not a syntax error. However, regardless of whether you specify NOT NULL, when you specify PRIMARY KEY, the SQL Server includes NOT NULL checking by default.</p></td>
</tr>


<tr>
<tr><div id="note seven"></div>
<td><b>[[#Section 8|Section 8]]</b></td> <td>If the SYSNAME extension is omitted in a multicolumn unique key definition, by default the Model 204 SQL Server assumes a concatenation of the constituent column names, separating the names with an ampersand (&amp;) character. For example, the default concatenation of (SSN, AGE, NAME) is SSN&AGE&NAME.</td>
<td>[[#seven|<b>7</b>]]</td> <td>Unlike the standard, UNIQUE is independent of NOT NULL. If you specify UNIQUE, NOT NULL is not implied. In addition, PRIMARY KEY is syntactically independent of NOT NULL. Specifying PRIMARY KEY without NOT NULL is not a syntax error. However, regardless of whether you specify NOT NULL, when you specify PRIMARY KEY, the SQL Server includes NOT NULL checking by default.</td></tr>
</tr>


<tr>
<tr><div id="note eight"></div>
<td><b>[[#Section 9|Section 9]]</b></td> <td>REFERENCES and the referential constraint definition apply only to nested tables. Specification of a REFERENCES clause in a context other than for a nested table produces a warning message.</td>
<td>[[#eight|<b>8</b>]]</td> <td>If the SYSNAME extension is omitted in a multicolumn unique key definition, by default the Model 204 SQL Server assumes a concatenation of the constituent column names, separating the names with an ampersand (&amp;) character. For example, the default concatenation of (SSN, AGE, NAME) is SSN&AGE&NAME.</td></tr>
</tr>


<tr>
<tr><div id="note nine"></div>
<td nowrap><b>[[#Section 10|Section 10]]</b></td> <td>A query expression is a query specification or a UNION of query specifications, where query specification is a SELECT statement (with no ORDER BY clause).</td>
<td>[[#nine|<b>9</b>]]</td> <td>REFERENCES and the referential constraint definition apply only to nested tables. Specification of a REFERENCES clause in a context other than for a nested table produces a warning message.</td></tr>
</tr>


<tr>
<tr><div id="note ten"></div>
<td><b>[[#Section 11|Section 11]]</b></td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td>
<td>[[#ten|<b>10</b>]]</td> <td>A query expression is a query specification or a UNION of query specifications, where query specification is a SELECT statement (with no ORDER BY clause).</td></tr>
</tr>


<tr>
<tr><div id="note eleven"></div>
<td><b>[[#Section 12|Section 12]]</b></td> <td>DROP SCHEMA physically deletes from CCACAT the definition of all tables, views, and associated grant statements.
<td>[[#eleven|<b>11</b>]]</td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td></tr>
<p>DROP TABLE deletes the table definition record from CCACAT and deletes privileges and constraints associated with the table.</p>
DROP VIEW deletes any subordinate views (that is, VIEWs of VIEWs) from CCACAT as well as privileges granted for the view.</td>
</tr>  


<tr>
<tr><div id="note twelve"></div>
<td><b>[[#Section 13|Section 13]]</b></td> <td>All columns are added to a table in the last position of the column list. If you DROP a column and then ADD an updated version of that column, the updated column might occupy a different position in the table than it did before you issued DROP and ADD.  
<td>[[#twelve|<b>12</b>]]</td> <td>DROP SCHEMA physically deletes from CCACAT the definition of all tables, views, and associated grant statements. DROP TABLE deletes the table definition record from CCACAT and deletes privileges and constraints associated with the table. DROP VIEW deletes any subordinate views (that is, VIEWs of VIEWs) from CCACAT as well as privileges granted for the view.</td></tr>


<p>Such a change in order of the column data can introduce errors into queries that use SELECT  * or INSERT (without a column list) and that depend on the correct position of the column data. Use MODIFY for changes to column definitions other than deletions and additions.</p></td>
<tr><div id="note thirteen"></div>
</tr>
<td>[[#thirteen|<b>13</b>]]</td> <td>All columns are added to a table in the last position of the column list. If you DROP a column and then ADD an updated version of that column, the updated column might occupy a different position in the table than it did before you issued DROP and ADD. Such a change in order of the column data can introduce errors into queries that use SELECT  * or INSERT (without a column list) and that depend on the correct position of the column data. Use MODIFY for changes to column definitions other than deletions and additions.</td></tr>


<tr>
<tr><div id="note fourteen"></div>
<td><b>[[#Section 14|Section 14]]</b></td> <td>The MODIFY <var class="term">columname</var> clause supports all aspects of column definition (see discussion in the previous note). Specify only the attributes being modified. You also can modify the field mapping clause and data type.</td>
<td>[[#fourteen|<b>14</b>]]</td> <td>The MODIFY <var class="term">columname</var> clause supports all aspects of column definition (see discussion in the previous note). Specify only the attributes being modified. You also can modify the field mapping clause and data type.</td></tr>
</tr>  


<tr>
<tr><div id="note fifteen"></div>
<td><b>[[#Section 15|Section 15]]</b></td> <td>Unlike the standard, no REFERENCES privileges are supported.</td>
<td>[[#fifteen|<b>15</b>]]</td> <td>Unlike the standard, no REFERENCES privileges are supported.</td></tr>
</tr>
</table>
</table>



Latest revision as of 15:18, 17 March 2016

Overview

This topic outlines in complete detail the syntax of Model 204 SQL DDL. Consult the American National Standards ANSI X3.135-1989 Database Language SQL document for syntax details not outlined here. The notation conventions used in this topic are listed in Notation conventions.

The following rules apply to this syntax:

  1. SQL object names (that is, schema name, table name, and so on) conform to rules for an SQL identifier:
    a name must be 18 characters or fewer, and it cannot be identical to an SQL keyword (the list of keywords is detailed in the standard). Model 204 SQL Reserved Words are listed in SQL reserved words.
  2. End of line (newline) is implementation defined.

    Note: The end of line character for use with DVI is the semicolon (;).

  3. Numbers in square brackets to the right of some of the syntax lines are keys to notes that appear at the end of the syntax diagrams.
  4. Model 204 SQL DDL extensions to the standard are printed in bold italics; statements or clauses that are not part of the ANSI SQL 1989 standard or the ANSI SQL 1992 standard, but that are anticipated to be part of the emerging standard are printed in italics.

DDL syntax

tablename ::= [schemaname.] <table>

table ::= basetable | viewname

schema ::= CREATE SCHEMA <schema-authorization-clause> [ schema-element ... ]

schema-authorization-clause ::= schemaname | AUTHORIZATION authorization-id[1] | schemaname AUTHORIZATION authorization-id[1]

schema-element ::= <table-definition> | <view-definition> | <privilege-definition>

table-definition ::= CREATE TABLE <tablename> [ <file-mapping-clause> | NESTED USING <nested-key> ] ( <table-element> ,... )

file-mapping-clause ::= SYSNAME 'filename'

nested-key ::= columname

table-element ::= <column-definition> | <table-constraint-definition>

column-definition ::=[2] columname <datatype> [ <field-mapping-clause> ] [ <column-constraint> ... ]

datatype ::= CHAR[ACTER] [(length)][3] | NUM[ERIC] [(precision [,scale])][4] | DEC[IMAL] [(precision [,scale])] | INT[EGER] | SMALLINT | FLOAT [(precision)][5] | REAL | DOUBLE PRECISION | BLOB | CLOB

field-mapping-clause ::= SYSNAME 'fieldname'

column-constraint ::=[6] NOT NULL | <unique-specification>[7] | <references-specification>

table-constraint-definition ::= <unique-constraint-definition> | <referential-constraint-definition>

unique-constraint-definition ::= <unique-specification> (<column-list>) [ SYSNAME 'fieldname' ][8]

unique-specification ::= UNIQUE | PRIMARY KEY [SYSTEM]

referential-constraint-definition ::=[9] FOREIGN KEY (columname) <references-specification>

references-specification ::= REFERENCES parent-table-name[9] [<referential-triggered-action>]

referential-triggered-action ::= <update-rule> [ <delete-rule> ] | <delete-rule> [ <update-rule> ]

update-rule ::= ON UPDATE CASCADE

delete-rule ::= ON DELETE CASCADE

view-definition ::= CREATE VIEW <viewname> [(<column-list>)] AS <query-expression> [WITH CHECK OPTION][10]

set user statement ::= SET USER authorization-id[11]

set schema statement ::= SET SCHEMA schemaname

drop schema statement ::= DROP SCHEMA schemaname[12]

drop table statement ::= DROP TABLE <tablename>[12]

drop view statement ::= DROP VIEW <viewname>[12]

alter table statement ::= ALTER TABLE <tablename> <alter-table-action>

alter-table-action ::= ADD <column-definition>[13] | DROP columname | MODIFY <column-parameters>

column-parameters ::= MODIFY columname[14] [<datatype>] [<field-mapping-clause>] [<modify-attribute-list> ...]

modify-attribute-list ::= [ NOT ] NULL | [ NOT ] UNIQUE

privilege-definition ::= GRANT <privileges> ON <object-name> TO <grantee> ,... [ WITH GRANT OPTION ]

privileges ::= ALL PRIVILEGES | <action> ,...

action ::= SELECT | INSERT | DELETE | UPDATE [(<column-list>)][15]

column list ::= columname,...

object-name ::= <tablename> | <viewname>

grantee ::= PUBLIC | authorization-id

revoke statement ::= REVOKE [ GRANT OPTION FOR ] <privileges> ON <object-name> FROM <grantee> ,...

Notes for syntax display

The numbers for the comments below correspond (and link) to the footnote numbers in the preceding syntax listing.

1 Since the AUTHORIZATION value maps to a Model 204 user ID, it can be no longer than 10 characters.
2 The column definition DEFAULT clause is not supported in Model 204 SQL.
3 Referred to as character string type in the standard. The default length is 1.
4 NUMERIC, DECIMAL, INTEGER, and SMALLINT are referred to as exact numeric types in the standard. The precision values are interpreted as decimal precision.
5 FLOAT, REAL, and DOUBLE PRECISION are referred to as approximate numeric types in the standard. The precision value is interpreted as binary precision.
6 CHECK column constraint is not supported in Model 204 SQL.
7 Unlike the standard, UNIQUE is independent of NOT NULL. If you specify UNIQUE, NOT NULL is not implied. In addition, PRIMARY KEY is syntactically independent of NOT NULL. Specifying PRIMARY KEY without NOT NULL is not a syntax error. However, regardless of whether you specify NOT NULL, when you specify PRIMARY KEY, the SQL Server includes NOT NULL checking by default.
8 If the SYSNAME extension is omitted in a multicolumn unique key definition, by default the Model 204 SQL Server assumes a concatenation of the constituent column names, separating the names with an ampersand (&) character. For example, the default concatenation of (SSN, AGE, NAME) is SSN&AGE&NAME.
9 REFERENCES and the referential constraint definition apply only to nested tables. Specification of a REFERENCES clause in a context other than for a nested table produces a warning message.
10 A query expression is a query specification or a UNION of query specifications, where query specification is a SELECT statement (with no ORDER BY clause).
11 Modifies the current SQL authorization ID. System manager privileges are required.
12 DROP SCHEMA physically deletes from CCACAT the definition of all tables, views, and associated grant statements. DROP TABLE deletes the table definition record from CCACAT and deletes privileges and constraints associated with the table. DROP VIEW deletes any subordinate views (that is, VIEWs of VIEWs) from CCACAT as well as privileges granted for the view.
13 All columns are added to a table in the last position of the column list. If you DROP a column and then ADD an updated version of that column, the updated column might occupy a different position in the table than it did before you issued DROP and ADD. Such a change in order of the column data can introduce errors into queries that use SELECT * or INSERT (without a column list) and that depend on the correct position of the column data. Use MODIFY for changes to column definitions other than deletions and additions.
14 The MODIFY columname clause supports all aspects of column definition (see discussion in the previous note). Specify only the attributes being modified. You also can modify the field mapping clause and data type.
15 Unlike the standard, no REFERENCES privileges are supported.

See also