How to create FOREIGN KEYS for non-existent table during initial database setup?

Question

I have a small database originally developed using SQLite. My desktop application automatically sets-up the database tables when the user initially launches the application.

Problem Several tables have FOREIGN KEY constraints. Assume I have Table A, Table B, and Table C. A has a FOREIGN KEY constraint with C. But as I do the initial table generation, SQLExceptions are thrown each time saying something like:

Constraint 'SQL130319142644383' is invalid: referenced table PREDICTIONRUN does not exist.

(Note: I cannot simply reorganize the tables because of other FOREIGN KEY issues.)

So, how do I create the tables with FOREIGN KEY constraints?

Example

if (! this.checkIfTableExists("DocumentClassification", conn)) {
                sql.setLength(0) ;
                sql.append("CREATE TABLE DocumentClassification (  " ) ;
                if (dbtype.equalsIgnoreCase("javadb")) {
                    sql.append("    classificationid INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) , " ) ; 

                } else {
                    sql.append("    classificationid INTEGER PRIMARY KEY, " ) ; 
                }
                sql.append("       classid INTEGER, " ) ;
                sql.append("       documentid INTEGER,  " ) ;
                sql.append("       predictionrunid INTEGER, " ) ;

                sql.append("       matterid VARCHAR(512), " ) ;
                sql.append("       datereadable VARCHAR(2048),  " ) ;
                sql.append("       date BIGINT,  " ) ;
                sql.append("       note VARCHAR(" + maxint + "),  " ) ;
                sql.append("       FOREIGN KEY ( classid) REFERENCES Classes (ClassID)," ) ;
                sql.append("       FOREIGN KEY ( documentid) REFERENCES MatterDataset (id),  " ) ;
                sql.append("       FOREIGN KEY (predictionrunid) REFERENCES PredictionRun (predictionrunid)" ) ;
                sql.append("    )  " ) ;
                // Create the table
                sqlstatement.executeUpdate(sql.toString()); 
            }

Databases Used JavaDB/Derby SQLite (via Xerial Driver) (no problem here because SQLite does not seem to enforce the constraint on creation)

Development Java 7

Answer 1

Assume I have Table A, Table B, and Table C. A has a FOREIGN KEY constraint with C.

The easy solution is to create Table C before Table A.

There are situations were you can't: for example, two tables that have foreign keys to eachother. Those situations are rather unusual. If you encounter one, you can create the tables without foreign keys. After creating all tables, you can then alter table the foreign keys in:

alter table YourTable add constraint FK_YourTable_Column1
    foreign key references OtherTable(Column1)