SQL DDL syntax: Difference between revisions

From m204wiki
Jump to navigation Jump to search
No edit summary
Line 31: Line 31:
   <span class="term">schemaname</span></p>
   <span class="term">schemaname</span></p>


===1===
===Section 1===
<p class="syntax"> | AUTHORIZATION authorization-id</p>
<p class="syntax"> | AUTHORIZATION authorization-id</p>


Line 56: Line 56:
   | <table-constraint-definition></p>
   | <table-constraint-definition></p>


===2===
===Section 2===
<p class="syntax">column-definition ::=
<p class="syntax">column-definition ::=
columname <datatype>
columname <datatype>
Line 64: Line 64:
datatype ::=</p>
datatype ::=</p>


===3===
===Section 3===
<p class="syntax">  CHAR[ACTER] [(length)]</p>
<p class="syntax">  CHAR[ACTER] [(length)]</p>


===4===
===Section 4===
<p class="syntax">  | NUM[ERIC] [(precision [,scale])]
<p class="syntax">  | NUM[ERIC] [(precision [,scale])]
   | DEC[IMAL] [(precision [,scale])]
   | DEC[IMAL] [(precision [,scale])]
Line 73: Line 73:
   | SMALLINT</p>
   | SMALLINT</p>


===5===
===Section 5===
<p class="syntax">  | FLOAT [(precision)]
<p class="syntax">  | FLOAT [(precision)]
   | REAL
   | REAL
Line 83: Line 83:
  <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' </p>
  <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' </p>


===6===
===Section 6===
<p class="syntax">column-constraint ::=
<p class="syntax">column-constraint ::=
   NOT NULL</p>
   NOT NULL</p>


===7===
===Section 7===
<p class="syntax">  | <unique-specification>
<p class="syntax">  | <unique-specification>
   | <references-specification>
   | <references-specification>
Line 98: Line 98:
   <unique-specification> (<column-list>)</p>
   <unique-specification> (<column-list>)</p>


===8===
===Section 8===
<p class="syntax">  [ <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' ]
<p class="syntax">  [ <span class="term">SYSNAME</span> '<span class="term">fieldname</span>' ]


Line 104: Line 104:
   UNIQUE | PRIMARY KEY [<span class="term"><b>SYSTEM</b></span>]</p>
   UNIQUE | PRIMARY KEY [<span class="term"><b>SYSTEM</b></span>]</p>


===9===
===Section 9===
<p class="syntax">referential-constraint-definition ::=
<p class="syntax">referential-constraint-definition ::=
   FOREIGN KEY (columname)
   FOREIGN KEY (columname)
Line 126: Line 126:
   CREATE VIEW <viewname> [(<column-list>)]</p>
   CREATE VIEW <viewname> [(<column-list>)]</p>


===10===
===Section 10===
<p class="syntax">    AS <<span class="term"><b>query-expression</b></span>> [WITH CHECK OPTION]
<p class="syntax">    AS <<span class="term"><b>query-expression</b></span>> [WITH CHECK OPTION]


set user statement::=</p>
set user statement::=</p>


===11===
===Section 11===
<p class="syntax">  SET USER authorization-id
<p class="syntax">  SET USER authorization-id


Line 139: Line 139:
drop schema statement::=</p>
drop schema statement::=</p>


===12===
===Section 12===
<p class="syntax">  DROP SCHEMA schemaname
<p class="syntax">  DROP SCHEMA schemaname


Line 158: Line 158:
alter-table-action::=</p>
alter-table-action::=</p>


===13===
===Section 13===
<p class="syntax">  ADD <column-definition>
<p class="syntax">  ADD <column-definition>
   | DROP columname
   | DROP columname
Line 165: Line 165:
column-parameters::=</p>
column-parameters::=</p>


===14===
===Section 14===
<p class="syntax">  MODIFY columname
<p class="syntax">  MODIFY columname
   [<datatype>]  
   [<datatype>]  
Line 189: Line 189:
   | DELETE</p>
   | DELETE</p>


===15===
===Section 15===
<p class="syntax">  | UPDATE [(<column-list>)]
<p class="syntax">  | UPDATE [(<column-list>)]


Line 212: Line 212:
<table class="noBorder">
<table class="noBorder">
<tr>
<tr>
<td><b>[[#1|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><b>[[#Section 1|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>
<tr>
<td><b>[[#2|2]]</b></td> <td>The column definition DEFAULT clause is not supported in Model 204 SQL.</td>
<td><b>[[#Section 2|2]]</b></td> <td>The column definition DEFAULT clause is not supported in Model 204 SQL.</td>
</tr>
</tr>


<tr>
<tr>
<td><b>[[#3|3]]</b></td> <td>Referred to as <var class="term">character string type</var> in the standard. The default length is 1.</td>
<td><b>[[#Section 3|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>
<td><b>[[#4|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><b>[[#Section 4|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>
<td><b>[[#5|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><b>[[#Section 5|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>
<td><b>[[#6|6]]</b></td> <td>CHECK column constraint is not supported in Model 204 SQL.</td>
<td><b>[[#Section 6|6]]</b></td> <td>CHECK column constraint is not supported in Model 204 SQL.</td>
</tr>
</tr>


<tr>
<tr>
<td><b>[[#7|7]]</b></td> <td>Unlike the standard, UNIQUE is independent of NOT NULL. If you specify UNIQUE, NOT NULL is not implied.
<td><b>[[#Section 7|7]]</b></td> <td>Unlike the standard, UNIQUE is independent of NOT NULL. If you specify UNIQUE, NOT NULL is not implied.
<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>
<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>
<tr>
<td><b>[[#8|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><b>[[#Section 8|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>
<td><b>[[#9|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><b>[[#Section 9|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>
<td><b>[[#10|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><b>[[#Section 10|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>
<td><b>[[#11|11]]</b></td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td>
<td><b>[[#Section 11|11]]</b></td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td>
</tr>
</tr>


<tr>
<tr>
<td><b>[[#12|12]]</b></td> <td>DROP SCHEMA physically deletes from CCACAT the definition of all tables, views, and associated grant statements.
<td><b>[[#Section 12|12]]</b></td> <td>DROP SCHEMA physically deletes from CCACAT the definition of all tables, views, and associated grant statements.
<p>DROP TABLE deletes the table definition record from CCACAT and deletes privileges and constraints associated with the table.</p>
<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>
DROP VIEW deletes any subordinate views (that is, VIEWs of VIEWs) from CCACAT as well as privileges granted for the view.</td>
Line 263: Line 263:


<tr>
<tr>
<td><b>[[#13|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><b>[[#Section 13|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.  


<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>
<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>
Line 269: Line 269:


<tr>
<tr>
<td><b>[[#14|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><b>[[#Section 14|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>
<td><b>[[#15|15]]</b></td> <td>Unlike the standard, no REFERENCES privileges are supported.</td>
<td><b>[[#Section 15|15]]</b></td> <td>Unlike the standard, no REFERENCES privileges are supported.</td>
</tr>
</tr>
</table>
</table>

Revision as of 21:23, 1 April 2015

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 Documentation 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 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.
  2. End of line (newline) is implementation defined.

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

  3. 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.
  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

Section 1

| AUTHORIZATION authorization-id

1

| schemaname AUTHORIZATION authorization-id 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>

Section 2

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

Section 3

CHAR[ACTER] [(length)]

Section 4

| NUM[ERIC] [(precision [,scale])] | DEC[IMAL] [(precision [,scale])] | INT[EGER] | SMALLINT

Section 5

| FLOAT [(precision)] | REAL | DOUBLE PRECISION | BLOB | CLOB field-mapping-clause:= SYSNAME 'fieldname'

Section 6

column-constraint ::= NOT NULL

Section 7

| <unique-specification> | <references-specification> table-constraint-definition ::= <unique-constraint-definition> | <referential-constraint-definition> unique-constraint-definition ::= <unique-specification> (<column-list>)

Section 8

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

Section 9

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

9

REFERENCES parent-table-name [<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>)]

Section 10

AS <query-expression> [WITH CHECK OPTION] set user statement::=

Section 11

SET USER authorization-id set schema statement::= SET SCHEMA schemaname drop schema statement::=

Section 12

DROP SCHEMA schemaname drop table statement::=

12

DROP TABLE <tablename> drop view statement::=

12

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

Section 13

ADD <column-definition> | DROP columname | MODIFY <column-parameters> column-parameters::=

Section 14

MODIFY columname [<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

Section 15

| UPDATE [(<column-list>)] 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 numbers in bold 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