SQL DDL syntax: Difference between revisions

Revision as of 15:22, 16 July 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 Notation conventions.

The following rules apply to this syntax:

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.
End of line (newline) is implementation defined.
Note: The end of line character for use with DVI is the semicolon (;).
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.
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

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 section numbers in the preceding syntax listing.

Section 1	Since the AUTHORIZATION value maps to a Model 204 user ID, it can be no longer than 10 characters.
Section 2	The column definition DEFAULT clause is not supported in Model 204 SQL.
Section 3	Referred to as `character string type` in the standard. The default length is 1.
Section 4	NUMERIC, DECIMAL, INTEGER, and SMALLINT are referred to as `exact numeric` types in the standard. The precision values are interpreted as decimal precision.
Section 5	FLOAT, REAL, and DOUBLE PRECISION are referred to as `approximate numeric` types in the standard. The precision value is interpreted as binary precision.
Section 6	CHECK column constraint is not supported in Model 204 SQL.
Section 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.
Section 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.
Section 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.
Section 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).
Section 11	Modifies the current SQL authorization ID. System manager privileges are required.
Section 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.
Section 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.
Section 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.
Section 15	Unlike the standard, no REFERENCES privileges are supported.

@@ Line 1: / Line 1: @@
+__NOTOC__
 ==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]].