Foreign key dependencies

Abstract

When recreating a table or its primary key, Informix Dynamic Server (IDS) doesn’t warn you that any foreign key constraints that reference it will be dropped. If you don’t have AGS Server Studio (which can show all types of object dependencies graphically), this article provides a convenient shell script to find this out beforehand.

Content

Examples in this article were produced using the provided “stores_demo” database in a Docker container from ibmcom/informix-developer-database.

This is the shell script:

The script is so named because it primarily relies on the “sysreferences” system catalog table.

Example output follows which lists all foreign key constraints in the database:

The output columns are:

You must supply the database name. Table names can be added to limit results only to those on either side of such relationships, for example:

You can specify wildcards conforming to the MATCHES operator (similar to shell file name generation):

You may want to tune column widths for actual maximum object name lengths in your databases. The script listing above has widths which are usually sufficient without causing excessively wide output:

For this article, the following was used for brevity:

Lines will be longer than 80 characters, so DBACCESS_COLUMNS is set in the script to keep each row on one line.

You can specify CSV output for loading into Excel, which includes column names in the first line:

Let’s say you needed to drop and recreate “manufact”. If so, the above tells you that “stock” has a FOREIGN KEY referencing “manufact”. You can tell from the constraint and index names that they were not stated explicitly, but used the following syntax:

It is of course better practice to name both, for example with:

That would then have shown as:

Whichever syntax was used, the “ALTER … ADD CONSTRAINT” statement (append NOVALIDATE to make it quicker) would need to be rerun if “manufact” or its primary key was dropped and recreated. The difference is that, with an explicit name, the index would have survived. See RENAME CONSTRAINT and RENAME INDEX to make their names explicit beforehand.

We previously published an article on View Dependencies which covers a similar problem. The following is a companion shell script to list them the same way rather than in SQL:

The script is so named because it primarily relies on the “sysdepend” system catalog table.

There are no views in the standard “stores_demo” database, but the following is the result after creating the examples given in the previous article on View Dependencies:

“Type” specifies whether the dependent object is a table or view.

If you recreated the “customer” table, you would have to recreate both those views afterwards.

Conclusion

This article provides shell scripts to list both foreign key and view dependencies so you can plan what might need recreating after a change.

It is clearly a good idea to ensure you always have a safe copy of database schemas: see Recording Informix schema changes. You would then have the SQL for any missing foreign keys or views detected later.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Contact Us

If you have any questions or would like to find out more about this topic, please contact us.