1

I am writing a tool for university which is supposed to take a MySQL database and copy it into a PostgreSQL database and vice versa.

I have now read all the relevant information about the tables / columns into Java objects and now want to start making my create statements, however I have one slight problem.

If a table references other tables using a foreign key relation, those other tables need to be created prior to the table, otherwise the DBMS will throw an error. So I have to somehow find a way to sort my table objects, either inside my Java code or by using a clever SQL query.

I have tried using their creation order in the original table:

USE project;
SELECT create_time, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'project';

Output

However this outputs the creation time only exact to the second, which doesn't help me if they were all created using a script.

This is how I store my tables:

public Table(String name, Column[] columns, ArrayList<String> primaryKeys, ArrayList<String[]> foreignKeys) {
    this.name = name;
    this.columns = columns;
    this.primaryKeys = primaryKeys;
    this.foreignKeys = foreignKeys;
}

The foreign key attribute is an ArrayList of String-Arrays of length 3, containing the column with the foreign key constraint and the table + column referencing it.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Christian Kammerer
  • 135
  • 1
  • 8

1 Answers1

2

Alternatively, you can create all the tables with their primary keys.

Then, you can use alter table add constraint to add foreign key constraints to the tables in any order.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If I do not find a more elegant solution this will probably be what I do. Thank you – Christian Kammerer Dec 22 '20 at 02:19
  • 2
    This approach may also prove much faster. – Basil Bourque Dec 22 '20 at 02:31
  • I would probably take the approach of this Answer, but, FYI, an alternative is to make the constraint `DEFERRABLE`. See this similar Question, [*How do I disable referential integrity in Postgres 8.2?*](https://stackoverflow.com/q/139884/642706). See [`CREATE TABLE`](https://www.postgresql.org/docs/current/sql-createtable.html) command in Postgres. – Basil Bourque Dec 22 '20 at 02:34