/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* TESTEXP2012 database
*
* Generated on 01/03/2012 08:37:54
* By the user LUCIAN
*
************************************************************/

SCRIPT
BEGIN

/************************************************************
* Tables
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "categories"
(
"Name" VARCHAR(20) COLLATE "UNI_CI" NOT NULL
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 6
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "subcat"
(
"Category" VARCHAR(40) COLLATE "UNI_CI" NOT NULL,
"Name" VARCHAR(40) COLLATE "UNI_CI" NOT NULL
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 6
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "stuff"
(
"IDCol" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Category" VARCHAR(20) COLLATE "UNI_CI" NOT NULL,
"SubCategory" VARCHAR(40) COLLATE "UNI_CI" NOT NULL,
"DateWhen" DATE DEFAULT CURRENT_DATE
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';


/************************************************************
* Table Rows
************************************************************/

EXECUTE IMMEDIATE 'INSERT INTO "categories" VALUES (''ALFA'')';

EXECUTE IMMEDIATE 'INSERT INTO "categories" VALUES (''BETA'')';

EXECUTE IMMEDIATE 'INSERT INTO "categories" VALUES (''GAMA'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''ALFA'',
                  ''ONE'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''ALFA'',
                  ''THREE'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''ALFA'',
                  ''TWO'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''BETA'',
                  ''ONE'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''BETA'',
                  ''THREE'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''BETA'',
                  ''TWO'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',
                  ''FOUR'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',
                  ''ONE'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',
                  ''THREE'')';

EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',
                  ''TWO'')';

EXECUTE IMMEDIATE 'INSERT INTO "stuff" VALUES (1,
                  ''ALFA'',
                  ''ONE'',
                  DATE ''2012-03-01'')';

EXECUTE IMMEDIATE 'INSERT INTO "stuff" VALUES (2,
                  ''BETA'',
                  ''TWO'',
                  DATE ''2012-03-01'')';

EXECUTE IMMEDIATE 'INSERT INTO "stuff" VALUES (3,
                  ''GAMA'',
                  ''THREE'',
                  DATE ''2012-03-01'')';


/************************************************************
* Views
************************************************************/


/************************************************************
* Functions
************************************************************/


/************************************************************
* Procedures
************************************************************/


/************************************************************
* Table triggers, indexes, and constraints
************************************************************/

-- Creating non-foreign key constraints for categories table

EXECUTE IMMEDIATE 'ALTER TABLE "categories"
ADD CONSTRAINT "PKey" PRIMARY KEY ("Name")';

-- Creating non-foreign key constraints for subcat table

EXECUTE IMMEDIATE 'ALTER TABLE "subcat"
ADD CONSTRAINT "PKey" PRIMARY KEY ("Category", "Name"),
ADD CONSTRAINT "uqSub" UNIQUE ("Name", "Category")';

-- Creating non-foreign key constraints for stuff table

EXECUTE IMMEDIATE 'ALTER TABLE "stuff"
ADD CONSTRAINT "PKey" PRIMARY KEY ("IDCol")';

-- Creating foreign key constraints for subcat table

EXECUTE IMMEDIATE 'ALTER TABLE "subcat"
ADD CONSTRAINT "fkCat" FOREIGN KEY ("Category")
REFERENCES "categories" ("Name")
ON UPDATE RESTRICT
ON DELETE RESTRICT';

-- Creating foreign key constraints for stuff table

EXECUTE IMMEDIATE 'ALTER TABLE "stuff"
ADD CONSTRAINT "fkCat" FOREIGN KEY ("Category")
REFERENCES "categories" ("Name")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "fkSub" FOREIGN KEY ("SubCategory")
REFERENCES "subcat" ("Name", "Category")
ON UPDATE NO ACTION
ON DELETE NO ACTION';

END

/************************************************************
* End of generated SQL
************************************************************/

---

 Comments

---

The issue was with defining a foreign key that has less columns than the primary/unique key of the target table of the foreign key constraint. In this particular case, it's the fkSub FK in the Stuff table.

---

 Resolution

---

Fixed Problem on 3/3/2012 in version 2.09 build 1

---

 Products Affected

---

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial