How do I check what depends on a table before I change it?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A ticket asks to delete a country, or to change a country's code. Before you touch the row, you need to know what depends on it: which other tables hold keys that point to it, and how many of their rows would be left pointing to nothing. Missing one means a failed delete at best, and orphan rows at worst.

You will list the foreign keys that the database declares, find the relationships it does not declare through column names, and count the rows that depend on one country. Along the way you will find that WORLD already holds rows that point to countries that do not exist, which only the undeclared relationship reveals. Every command of this page only reads.

Short answer

SHOW REFERENCES COUNTRY; lists the declared foreign keys that point to COUNTRY, and SHOW FK COUNTRY; those COUNTRY holds. FIND COLUMN COUNTRY%CODE; finds the columns that hold a country code without a declared key. Then count the dependent rows with SQL.

1. Ask the database what points to the table

Connected to WORLD, list the foreign keys of other tables that point to COUNTRY:

SHOW REFERENCES COUNTRY;

It lists one table, REGION: its column COUNTRY_CODE2 refers to COUNTRY.CODE2. Look at that key from the REGION side:

SHOW FK REGION;

SHOW FK lists the foreign keys of REGION, with the column and the column it refers to:

WORLD> SHOW FK REGION;
|-------------|-|-------------|----------------|-----------------|
|FK_NAME      |#|COLUMN       |REFERENCES_TABLE|REFERENCES_COLUMN|
|-------------|-|-------------|----------------|-----------------|
|CONSTRAINT_8F|1|COUNTRY_CODE2|COUNTRY         |CODE2            |
|-------------|-|-------------|----------------|-----------------|

The # column is the position of a column within a key made of several columns; here the key has one column. CONSTRAINT_8F is the name the database gave to a key created without a name.

2. What the table itself refers to

The other direction matters too: a change of COUNTRY must keep its own keys valid. SHOW FK COUNTRY; shows that COUNTRY.CURRENCY_CODE refers to CURRENCY.CODE:

SHOW FK COUNTRY;

To search the foreign keys of the schema by name instead, FIND FK COUNTRY; lists those whose constraint, table or column names contain COUNTRY.

3. The limit: only declared keys appear

These commands read the foreign keys the database declares. A relationship enforced only by the application, by a trigger, or by nothing at all does not appear. Look for columns that hold a country code under any name:

FIND COLUMN COUNTRY%CODE;

It finds REGION.COUNTRY_CODE2, already known, and two columns that SHOW REFERENCES did not list: CITY.COUNTRYCODE and COUNTRYLANGUAGE.COUNTRYCODE. They hold country codes, yet WORLD declares no foreign key for them: for the database, CITY and COUNTRY are unrelated.

4. Count the rows that depend on one country

For France, count the rows of each dependent table with ordinary SQL: the cities and languages through the undeclared relationships, and the regions through the declared key, which uses CODE2:

SELECT COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE = 'FRA';
SELECT COUNT(*) AS LANGUAGES FROM COUNTRYLANGUAGE WHERE COUNTRYCODE = 'FRA';
SELECT COUNT(*) AS REGIONS FROM REGION WHERE COUNTRY_CODE2 = 'FR';

They return 40 cities, 6 languages and 108 regions. Deleting France would fail on the 108 regions, since the declared key protects them, and would silently leave the 40 cities and 6 languages pointing to a country that no longer exists.

5. The damage an undeclared relationship already did

Without a key, nothing checks that every city's country exists. Ask the question the database never asked:

SELECT C.COUNTRYCODE, COUNT(*) AS CITIES
  FROM CITY C LEFT JOIN COUNTRY K ON K.CODE = C.COUNTRYCODE
 WHERE K.CODE IS NULL
 GROUP BY C.COUNTRYCODE ORDER BY C.COUNTRYCODE;

Two codes of CITY match no country:

WORLD> SELECT C.COUNTRYCODE, COUNT(*) AS CITIES FROM CITY C LEFT JOIN COUNTRY K ON K.CODE = C.COUNTRYCODE WHERE K.CODE IS NULL GROUP BY C.COUNTRYCODE ORDER BY C.COUNTRYCODE;
|-----------|--------------------|
|COUNTRYCODE|CITIES              |
|-----------|--------------------|
|ROM        |29                  |
|TMP        |1                   |
|-----------|--------------------|

2 rows fetched in 6 ms.

ROM and TMP are the old codes of Romania and East Timor, which COUNTRY now holds as ROU and TLS. The countries' codes were changed, the database accepted it, and the cities were left behind: exactly the risk of the change the ticket asks for. The same query on COUNTRYLANGUAGE, with L for the language table, finds 6 rows under ROM and 2 under TMP.

6. Conclusion for the ticket

• Changing COUNTRY.CODE of a country must also change CITY.COUNTRYCODE and COUNTRYLANGUAGE.COUNTRYCODE, which nothing will do for you.
• Changing COUNTRY.CODE2 is checked by the database through the REGION key.
• Deleting a country fails while regions refer to it, and leaves its cities and languages behind otherwise.

Variations

• Walk the model: run SHOW REFERENCES again on each referencing table, one level at a time.
• Every orphan check at once: Find orphan rows, duplicates and missing values saves these checks as a Script.
• Make the change safely: Verify that a data fix changed only the intended rows moves the orphan cities to the current codes with a before and after comparison.

Watch out

• Triggers and application rules are invisible to SHOW REFERENCES and SHOW FK.
• Without a schema, the current schema is used; qualify the table (PUBLIC.COUNTRY) when a name exists in several.
• FIND COLUMN finds columns by name only: a column called CTRY or NATION needs another search.

Related

• Exploring a database
• Explore an unfamiliar database
• SHOW REFERENCES, SHOW FK, FIND COLUMN

Commands used

• SHOW REFERENCES
• SHOW FK
• FIND FK
• FIND COLUMN

Found something wrong or unclear on this page? Tell us, with the page address.