SQL DDL syntax

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

• SQL Server overview
• SQL Server installation
• SQL catalog
• SQL representation of Model 204 data
• SQL Data Definition Language (DDL)
• SQL DDL from the Table Specification Facility
• SQL catalog reporting and querying
• SQL Data Manipulation Language (DML)
• SQL DDL syntax
• SQL reserved words
• SQL DDL mapping of the demonstration database