How do i determine whether a given entry has foreign keys pointing to it?

Question

I am currently working on a trigger function for a parent table, which should execute a check on relevant rows on a child table which has a foreign key to the parent table row which was updated.

I made small dbfiddle setting up my situation:

https://www.db-fiddle.com/f/sV8f9F7Lg6R6HE89nXNhhV/4

My data model consist instance of an given entity registrations, containing the different versions of a given entry. So i my case i habe the country, narnia and an airport narnia airport. Narnia airport has a foreing key to country, stating it part of narnia.

Every airport has a check function ensuring the the child table is withkn the lifetime of its parent, meaning that narnia airport cannot exist as long narnia does not exist...

Which the first query shows, but how i make an efficient check the other way? Currently i can change country to outside og the lifespan of its children making the children orphaned?

I had an idea of creating a trigger for every parent child relation, but it seems a bit heavy, and i am only interested in doing this check for the affected children, not every single one?

How do I know that something depends on this row, and perform my desired check, and vice versa, that nothing depends on this given row?

Answer 1

As you outlined, you will need to do your constraint checks in both directions: when an airport is updated, make sure the country is valid; and when a country is updated, you will need to make sure all airport registrations still have valid ranges in respect to the updated country range.

You may be able to achive that through CONSTRAINT clauses with functions, however you will have better control of the logic when you use TRIGGERs. e.g. a within a trigger function, you will always have the old and new row or value at your disposal, so you could easily skip the checks if the time ranges have not changed.

To check "the other side", you will have to do SQL queries within your trigger functions. Maybe add an INDEX on the foreign key column (e.g. on airport.country) to speed up the query if you have many rows in airport.

The Trigger on "country" will have access to the modified country's ID, so you can execute an SQL like: IF EXISTS(SELECT 1 FROM airport a WHERE country=NEW.entity_id AND ... /* insert range checking condition here*/ ) THEN RAISE ERROR ''; END IF; and you will not have to worry about performance.

https://www.postgresql.org/docs/12/plpgsql-trigger.html has a comprehensive example and documentation about trigger functions.

Answer 2

Maybe that helps you, if I got your question correctly.

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';