How to create multiple tables with the same schema using SQLite jdbc
- by Space_C0wb0y
I want to split a large table horizontally, and I would like to make sure that all three of them have the same schema. Currently I am using this piece of code to create the tables:
statement
.executeUpdate("CREATE TABLE AnnotationsMolecularFunction (Id INTEGER PRIMARY KEY ASC AUTOINCREMENT, "
+ "ProteinId NOT NULL, "
+ "GOId NOT NULL, "
+ "UNIQUE (ProteinId, GOId)"
+ "FOREIGN KEY(ProteinId) REFERENCES Protein(Id))");
There is one such statement for each table. This is bad, because if I decide to change the schema later (which will most certainly happen), I will have to change it three times, which begs for errors, so I would like a way to make sure that the other tables have the same schema without explicitly writing it again. I can use:
statement
.executeUpdate("CREATE TABLE AnnotationsBiologicalProcess AS SELECT * FROM AnnotationsMolecularFunction");
to create the other tables with the same columns, but the constraints are not aplied. I could of course just generate the same query-string three times with different table-names in Java, but I would like to know if there is an SQL-way of achieving this.
