Offline Tables and Foreign Keys

When you import a table from a live database schema to JDeveloper, information about any foreign keys will not necessarily be available. This means that if you work on one or more table definitions offline and then generate the information back to the original database, foreign keys may be lost. If you are in any doubt, you should not generate changes directly back to the database. Instead you should generate a SQL file that you can examine and if necessary change before running it against the database.

The following sections discuss the cases in which foreign key information may be lost.

Importing Tables at Both Ends of a Foreign Key

This is the simplest case, and you don't need to worry about losing important information. When JDeveloper imports tables that have foreign keys between them, information about the foreign key is also imported. Therefore the foreign key is correctly shown on a database diagram and on the Constraint Information page of the Edit Offline Table dialog.

Once you have finished working on the tables you can choose to generate your changes directly back to the database.

Importing a Table at One End of a Foreign Key

In the case that JDeveloper imports a table that has a foreign key, but the table at the other end of the key is not imported, the foreign key information is present but hidden. If the table at the other end of the foreign key is subsequently imported, then the foreign key information will be shown correctly on a database diagram that contains both tables, or on the Constraint Information page of the Edit Offline Table dialog. But if the table at the other end of the foreign key is not imported, the foreign key information is hidden, and when you generate your changes to the database, the foreign key will be dropped. If that is not what you intend you should generate a SQL file, rather than generating the changes directly to the database. You can then edit the SQL file before running it to remove the unwanted DROP statements.

Best Practice

From the information above you can see that if you are importing tables to act as the basis of a new database schema, then you don't need to worry about foreign keys to tables that you are not interested in. You can safely make your changes and generate the online tables in a new schema.

However if you are importing tables so that you can make the changes you want and then generate the changes back to the same database schema you should either:

Import all tables that have a foreign key relationship, whether you intend to change them or not, so that you generate the correct information about the foreign keys to a SQL file or directly to the database.
Import just the tables you want to change, but make choose to generate a SQL File. Make sure that you edit the generated SQL file to remove unwanted DROP statements before it is run against a database connection.