Appendix C. Modified code

Modified code (from appendix B) in SQL2 syntax, showing re_arrangement of Foreign Key references.

CREATE SCHEMA
AUTHORIZATION SQL2_ER

CREATE TABLE DOMAINS
     (
     DOMAIN_CATALOG       IDENTIFIER ,
     DOMAIN_NAME          IDENTIFIER ,
     DOMAIN_SCHEMA        IDENTIFIER ,
     CONSTRAINT_NAME      IDENTIFIER ,
     DOMAIN_DEFAULT       CHAR_DATA ,
     PRIMARY KEY (DOMAIN_CATALOG, DOMAIN_NAME, DOMAIN_SCHEMA),
     FOREIGN KEY (DOMAIN_CATALOG, DOMAIN_SCHEMA, CONSTRAINT_NAME) REFERENCES CHECK_CONSTRAINTS,
     FOREIGN KEY (DOMAIN_CATALOG, DOMAIN_SCHEMA) REFERENCES SCHEMATA
     )

CREATE TABLE TRANSLATIONS
     (
     TRANSLATION_CATALOG  IDENTIFIER ,
     TRANSLATION_NAME     IDENTIFIER ,
     TRANSLATION_SCHEMA   IDENTIFIER ,
     SRC_CHARSET_CATALOG  IDENTIFIER ,
     SRC_CHARSET_NAME     IDENTIFIER ,
     SRC_CHARSET_SCHEMA   IDENTIFIER ,
     TGT_CHARSET_CATALOG  IDENTIFIER ,
     TGT_CHARSET_NAME     IDENTIFIER ,
     TGT_CHARSET_SCHEMA   IDENTIFIER ,
     PRIMARY KEY (TRANSLATION CATALOG, TRANSLATION_NAME, TRANSLATION_SCHEMA),
     FOREIGN KEY (SRC_CHARSET_CATALOG, SRC_CHARSET_NAME, SRC_CHARSET_SCHEMA) REFERENCES CHARACTER_SETS,
     FOREIGN KEY (TGT_CHARSET_CATALOG, TGT_CHARSET_NAME, TGT_CHARSET_SCHEMA) REFERENCES CHARACTER_SETS,
     FOREIGN KEY (TRANSLATION_NAME, TRANSLATION_SCHEMA) REFERENCES SCHEMATA
     )

CREATE TABLE CHARACTER_SETS
     (
     CHARSET_CATALOG        IDENTIFIER ,
     CHARSET_NAME           IDENTIFIER ,
     CHARSET_SCHEMA         IDENTIFIER ,
     DEF_COLLATE_CATALOG    IDENTIFIER ,
     DEF_COLLATE_NAME       IDENTIFIER ,
     DEF_COLLATE_SCHEMA     IDENTIFIER ,
     FORM_OF_USE            IDENTIFIER ,
     NUMBER_OF_CHARS        CARDINAL ,
     PRIMARY KEY (CHARSET_CATALOG, CHARSET_NAME, CHARSET_SCHEMA),
     FOREIGN KEY (CHARSET_CATALOG, CHARSET_SCHEMA) REFERENCES SCHEMATA,
     FOREIGN KEY (DEF_COLLATE_CATALOG, DEF_COLLATE_NAME, DEF_COLLATE_SCHEMA) REFERENCES COLLATIONS
     )

CREATE TABLE COLLATIONS
     (
     COLLATION_CATALOG      IDENTIFIER ,
     COLLATION_NAME         IDENTIFIER ,
     COLLATION_SCHEMA       IDENTIFIER ,
     CHARSET_CATALOG        IDENTIFIER ,
     CHARSET_NAME           IDENTIFIER ,
     CHARSET_SCHEMA         IDENTIFIER ,
     PRIMARY KEY (COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA),
     FOREIGN KEY (CHARSET_CATALOG, CHARSET_NAME, CHARSET_SCHEMA), REFERENCES CHARACTER_SETS,
     FOREIGN KEY (COLLATION_CATALOG, COLLATION_SCHEMA) REFERENCES SCHEMATA
     )

CREATE TABLE DATA_TYPE_DESCRIPTOR
     (
     COLUMN_NAME            IDENTIFIER ,
     DTD_CATALOG            IDENTIFIER ,
     DTD_SCHEMA             IDENTIFIER ,
     TABLE_OR_DOMAIN_NAME   IDENTIFIER ,
     CHAR_MAX_LENGTH        CARDINAL , 
     CHAR_OCTET_LENGTH      CARDINAL , 
     COLlATION_CATALOG      IDENTIFIER ,
     COLLATION_NAME         IDENTIFIER ,
     COLLATION_SCHEMA       IDENTIFIER ,
     DATA_TYPE              CHAR_DATA ,
     DATETIME_PRECISION     CARDINAL ,
     NUMERIC_PRECISION      CARDINAL ,
     NUMERIC_PREC_RADIX     CARDINAL ,
     NUMERIC_SCALE          CARDINAL ,
     PRIMARY KEY (COLUMN_NAME, DTD_CATALOG, DTD_SCHEMA, TABLE_OR_DOMAIN_NAME),
     FOREIGN KEY (COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA) REFERENCES COLLATIONS
     )

CREATE TABLE CHECK_CONSTRAINTS
     (
     CONSTRAINT_CATALOG      IDENTIFIER ,
     CONSTRAINT_NAME         IDENTIFIER ,
     CONSTRAINT_SCHEMA       IDENTIFIER ,
     CHECK_CLAUSE            CHAR_DATA ,
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA),
     )

CREATE TABLE SCHEMATA
     (
     CATALOG_NAME            IDENTIFIER ,
     SCHEMA_NAME             IDENTIFIER ,
     DEF_TIME_DISP_TYPE      CHAR_DATA ,
     SCHEMA_OWNER            IDENTIFIER ,
     TIME_DISP_CATALOG       IDENTIFIER ,
     TIME_DISP_SCHEMA        IDENTIFIER ,
     TIME_DISP_TABLE         IDENTIFIER ,
     PRIMARY KEY (CATALOG_NAME, SCHEMA_NAME),
     FOREIGN KEY (SCHEMA_OWNER) REFERENCES USERS,
     FOREIGN KEY (TIME_DISP_CATALOG, TIME_DISP_TABLE, TIME_DISP_SCHEMA) REFERENCES TABLES
     )

CREATE TABLE VIEWS
     (
     TABLE_CATALOG     IDENTIFIER ,
     TABLE_NAME        IDENTIFIER ,
     TABLE_SCHEMA      IDENTIFIER ,
     CHECK_OPTION      CHAR_DATA ,
     UPDATABLE         CHAR_DATA ,
     VIEW_DEFINITION   CHAR_DATA ,
     PRIMARY KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (TABLE_CATALOG, TABLE_SCHEMA) REFERENCES SCHEMATA
     )

CREATE TABLE USERS
     (
     USER_NAME        IDENTIFIER ,
     PRIMARY KEY (USER_NAME)
     )

CREATE TABLE DOMAIN_PRIVILEGES
     (
     CATALOG          IDENTIFIER ,
     DOMAIN_NAME      IDENTIFIER ,
     DOMAIN_SCHEMA    IDENTIFIER ,
     GRANTEE          IDENTIFIER ,
     GRANTOR          IDENTIFIER ,
     PRIVILEGE        CHAR_DATA ,
     GRANTABLE        CHAR_DATA ,
     PRIMARY KEY (DOMAIN_CATALOG, DOMAIN_NAME, DOMAIN_SCHEMA, GRANTEE, GRANTOR, PRIVILEGE),
     FOREIGN KEY (DOMAIN_CATALOG, DOMAIN_NAME, DOMAIN_SCHEMA) REFERENCES DOMAINS,
     FOREIGN KEY (GRANTEE) REFERENCES USERS,
     FOREIGN KEY (GRANTOR) REFERENCES USERS
     )

CREATE TABLE ASSERTIONS
     (
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA),
     FOREIGN KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA) REFERENCES CHECK_CONSTRAINTS
     )

CREATE TABLE CHECK_TABLE_USAGE
     (
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     TABLE_CATALOG         IDENTIFIER ,
     TABLE_NAME            IDENTIFIER ,
     TABLE_SCHEMA          IDENTIFIER ,
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA) REFERENCES CHECK_CONSTRAINTS,
     FOREIGN KEY(TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA) REFERENCES TABLES
     )

CREATE TABLE CHECK_COLUMN_USAGE
     (
     COLUMN_NAME           IDENTIFIER ,
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     TABLE_CATALOG         IDENTIFIER ,
     TABLE_NAME            IDENTIFIER ,
     TABLE_SCHEMA          IDENTIFIER ,
     PRIMARY KEY (COLUMN_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (COLUMN_NAME, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA) REFERENCES COLUMNS,
     FOREIGN KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA) REFERENCES CHECK_CONSTRAINTS
     )

CREATE TABLE TABLE_PRIVILEGES
     (
     GRANTEE          IDENTIFIER ,
     GRANTOR          IDENTIFIER ,
     PRIVILEGE        CHAR_DATA ,
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     GRANTABLE        CHAR_DATA ,
     PRIMARY KEY (GRANTEE, GRANTOR, PRIVILEGE, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA) REFERENCES TABLES,
     FOREIGN KEY (GRANTEE) REFERENCES USERS,
     FOREIGN KEY (GRANTOR) REFERENCES USERS
     )

CREATE TABLE TABLES
     (
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     TABLE_TYPE       CHAR_DATA ,
     PRIMARY KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (TABLE_CATALOG, TABLE_SCHEMA) REFERENCES SCHEMATA
     )

CREATE TABLE VIEW_TABLE_USAGE
     (
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     VIEW_CATALOG     IDENTIFIER ,
     VIEW_NAME        IDENTIFIER ,
     VIEW_SCHEMA      IDENTIFIER ,
     PRIMARY KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, VIEW_CATALOG, VIEW_NAME, VIEW_SCHEMA)
     FOREIGN KEY (VIEW_CATALOG, VIEW_SCHEMA) REFERENCES SCHEMATA,
     FOREIGN KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA) REFERENCES TABLES
     )

CREATE TABLE TABLE_CONSTRAINTS
     (
     CONSTRAINT_CATALOG     IDENTIFIER ,
     CONSTRAINT_NAME        IDENTIFIER ,
     CONSTRAINT_SCHEMA      IDENTIFIER ,
     CONSTRAINT_TYPE        CHAR_DATA ,
     TABLE_NAME             IDENTIFIER ,
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA)
     FOREIGN KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA) REFERENCES TABLES
     )

CREATE TABLE COLUMN_PRIVILEGES
     (
     COLUMN_NAME      IDENTIFIER ,
     GRANTEE          IDENTIFIER ,
     GRANTOR          IDENTIFIER ,
     PRIVILEGE        CHAR_DATA ,
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     GRANTABLE        CHAR_DATA ,
     PRIMARY KEY (COLUMN_NAME, GRANTEE, GRANTOR, PRIVILEGE, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (COLUMN_NAME, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA) REFERENCES COLUMNS,
     FOREIGN KEY (GRANTEE) REFERENCES USERS, FOREIGN KEY (GRANTOR) REFERENCES USERS
     )

CREATE TABLE COLUMNS
     (
     COLUMN_NAME      IDENTIFIER ,
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     COLUMN_DEFAULT   CHAR_DATA ,
     DOMAIN_CATALOG   IDENTIFIER ,
     DOMAIN_NAME      IDENTIFIER ,
     DOMAIN_SCHEMA    IDENTIFIER ,
     POSITION         CARDINAL ,
     PRIMARY KEY (COLUMN_NAME, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA),
     FOREIGN KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA) REFERENCES TABLES,
     FOREIGN KEY (DOMAIN_CATALOG, DOMAIN_NAME, DOMAIN_SCHEMA) REFERENCES DOMAINS
     )

CREATE TABLE KEY_COLUMN_USAGE
     (
     COLUMN_NAME           IDENTIFIER ,
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     TABLE_NAME            IDENTIFIER ,
     POSITION              CARDINAL ,
     PRIMARY KEY (COLUMN_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, TABLE_NAME),
     FOREIGN KEY (COLUMN_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA) REFERENCES COLUMNS
     )

CREATE TABLE VIEW_COLUMN_USAGE
     (
     COLUMN_NAME      IDENTIFIER ,
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     VIEW_CATALOG     IDENTIFIER ,
     VIEW_NAME        IDENTIFIER ,
     VIEW_SCHEMA      IDENTIFIER ,
     PRIMARY KEY (COLUMN_NAME, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, VIEW_CATALOG, VIEW_NAME, VIEW SCHEMA),
     FOREIGN KEY (COLUMN_NAME, VIEW_CATALOG, VIEW_NAME, VIEW_SCHEMA) REFERENCES COLUMNS
     )

CREATE TABLE REF_CONSTRAINTS
     (
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     CONST_CATALOG_UK      IDENTIFIER ,
     CONST_NAME_UK         IDENTIFIER ,
     CONST_SCHEMA_UK       IDENTIFIER ,
     DELETE_RULE           CHAR_DATA ,
     MATCH_OPTION          CHAR_DATA ,
     UPDATE_RULE           CHAR_DATA ,
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA),
     FOREIGN KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA) REFERENCES TABLE_CONSTRAINTS,
     FOREIGN KEY (CONST_CATALOG_UK, CONST_NAME_UK, CONST_SCHEM_UK) REFERENCES TABLE_CONSTRAINTS
     )

Contents | 1 Introduction | 2 Review | 3 Research aims | 4 Program | 5 Work | References | Appendix A | Appendix B | Appendix C