SQL DDL syntax - Revision history

ELowell: /* Overview */

2016-03-17T15:18:11Z

Overview

← Older revision		Revision as of 15:18, 17 March 2016
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.

ELowell: /* Overview */

2015-07-16T22:34:33Z

Overview

← Older revision		Revision as of 22:34, 16 July 2015
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>

ELowell at 22:30, 16 July 2015

2015-07-16T22:30:47Z

Show changes

ELowell at 15:22, 16 July 2015

2015-07-16T15:22:05Z

← Older revision		Revision as of 15:22, 16 July 2015
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]].

ELowell: /* Overview */

2015-04-15T19:00:35Z

Overview

← Older revision		Revision as of 19:00, 15 April 2015
Line 1:		Line 1:
	==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 [[~~Documentation~~ conventions]]. ~~</p>~~		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]].
	<p>
	The following rules apply to this syntax:</p>		<p>The following rules apply to this syntax:</p>
	<ol>		<ol>
	<li>		<li>

ELowell: /* Notes for syntax display */

2015-04-01T21:26:58Z

Notes for syntax display

← Older revision		Revision as of 21:26, 1 April 2015
Line 212:		Line 212:
	<table class="noBorder">		<table class="noBorder">
	<tr>		<tr>
	<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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<td><b>[[#Section 2\|2]]</b></td> <td>The column definition DEFAULT clause is not supported in Model 204 SQL.</td>		<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>		<tr>
	<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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<td><b>[[#Section 6\|6]]</b></td> <td>CHECK column constraint is not supported in Model 204 SQL.</td>		<td><b>[[#Section 6\|Section 6]]</b></td> <td>CHECK column constraint is not supported in Model 204 SQL.</td>
	</tr>		</tr>

	<tr>		<tr>
	<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.		<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.
	<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>[[#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 (&) character. For example, the default concatenation of (SSN, AGE, NAME) is SSN&AGE&NAME.</td>		<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 (&) character. For example, the default concatenation of (SSN, AGE, NAME) is SSN&AGE&NAME.</td>
	</tr>		</tr>

	<tr>		<tr>
	<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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<td><b>[[#Section 11\|11]]</b></td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td>		<td><b>[[#Section 11\|Section 11]]</b></td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td>
	</tr>		</tr>

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

	<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>[[#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>		<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>
	</tr>		</tr>

	<tr>		<tr>
	<td><b>[[#Section 15\|15]]</b></td> <td>Unlike the standard, no REFERENCES privileges are supported.</td>		<td><b>[[#Section 15\|Section 15]]</b></td> <td>Unlike the standard, no REFERENCES privileges are supported.</td>
	</tr>		</tr>
	</table>		</table>

ELowell: /* Notes for syntax display */

2015-04-01T21:24:04Z

Notes for syntax display

← Older revision		Revision as of 21:24, 1 April 2015
Line 209:		Line 209:
	==Notes for syntax display==		==Notes for syntax display==

	The numbers for the comments below correspond (and link) to the numbers ~~in <b>bold</b>~~ in the preceding syntax listing.		The numbers for the comments below correspond (and link) to the section numbers in the preceding syntax listing.
	<table class="noBorder">		<table class="noBorder">
	<tr>		<tr>

ELowell at 21:23, 1 April 2015

2015-04-01T21:23:00Z

Show changes

ELowell: /* Notes for syntax display */

2015-04-01T21:18:12Z

Notes for syntax display

← Older revision		Revision as of 21:18, 1 April 2015
Line 276:		Line 276:
	</tr>		</tr>
	</table>		</table>

			==See also==
			{{Template:SQL topic list}}

	[[Category: Model 204 SQL processing]]		[[Category: Model 204 SQL processing]]

ELowell: Created page with "==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 fo..."

2015-03-24T16:58:15Z

Created page with "==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 fo..."

New page

==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:
<ol>
<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>
<li>
End of line (newline) is implementation defined.

Note: The end of line character for use with DVI is the semicolon (;).
</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>
<li>
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.</li>
</ol>

==DDL syntax==
tablename ::=
[schemaname.] <table>

table ::=
basetable | viewname

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

schema-authorization-clause ::=
schemaname

===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>

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

datatype ::=

===3===
 CHAR[ACTER] [(length)]

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

===5===
 | FLOAT [(precision)]
| REAL
| DOUBLE PRECISION
| BLOB
| CLOB

field-mapping-clause:=
SYSNAME 'fieldname' 

===6===
column-constraint ::=
NOT NULL

===7===
 | <unique-specification>
| <references-specification>

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

unique-constraint-definition ::=
<unique-specification> (<column-list>)

===8===
 [ SYSNAME 'fieldname' ]

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

===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>)]

===10===
 AS <query-expression> [WITH CHECK OPTION]

set user statement::=

===11===
 SET USER authorization-id

set schema statement::=
SET SCHEMA schemaname

drop schema statement::=

===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::=

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

column-parameters::=

===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

===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.
<table class="noBorder">
<tr>
<td>[[#1|1]]</td> <td>Since the AUTHORIZATION value maps to a Model 204 user ID, it can be no longer than 10 characters.</td>
</tr>

<tr>
<td>[[#2|2]]</td> <td>The column definition DEFAULT clause is not supported in Model 204 SQL.</td>
</tr>

<tr>
<td>[[#3|3]]</td> <td>Referred to as <var class="term">character string type</var> in the standard. The default length is 1.</td>
</tr>

<tr>
<td>[[#4|4]]</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>
<td>[[#5|5]]</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>
<td>[[#6|6]]</td> <td>CHECK column constraint is not supported in Model 204 SQL.</td>
</tr>

<tr>
<td>[[#7|7]]</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>
<td>[[#8|8]]</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 (&) character. For example, the default concatenation of (SSN, AGE, NAME) is SSN&AGE&NAME.</td>
</tr>

<tr>
<td>[[#9|9]]</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>
<td>[[#10|10]]</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>
<td>[[#11|11]]</td> <td>Modifies the current SQL authorization ID. System manager privileges are required.</td>
</tr>

<tr>
<td>[[#12|12]]</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>

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

[[Category: Model 204 SQL processing]]