3

Hello dear Stack Overflow community,

I searched many hours already but I couldn't find a solution my quite unique scenario.
Maybe you guys can help.

Let's assume we have two tables.

|domain_realm                     |   | password_policy                        |
|-------------------------------- |   | ---------------------------------------|
|id    domain  password_policy_id |   | id  min_chars  min_numbers  min_length |
|1     1       11                 |   | 11  3          2                       |
|2     1       12                 |   | 12  4          1                       |

One is called domain_realm, the other one password_policy.

It's a OneToOne relationship, therefore one domain_realm has only one password_policy.

Now comes the quite unique case in this scenario:
domain_realm holds the password_policy_id (more or less fk) instead of password_policy holding the foreign key of it's parent.

Why would someone do this?
Let's assume password_policy is part of a package for many other applications not working with domain realms. Therefore keeping the table clean of any specific rows no other application needs (e.g. fk_domain_relam) is mandatory.

Now it seems to be impossible to automatically delete (ON DELETE CASCADE) the correlating password_policy when deleting a domain_realm.

Is there a way to do this the SQL way or do I have to know this and make sure that password_policy gets deleted in my code?

That would be quite nasty because if I have to delete a domain_realm directly in the database (debug or support reasons) I've created an orphan and I have to know this. Otherwise the orphan will stay in that db forever.

Thank you very much up front and for even reading this.

sdk
  • 33
  • 6
  • you ask a question about your structure without providing the structure?.. – Vao Tsun May 04 '17 at 07:35
  • Dunno what you mean and/or need. There's a graphic and a quite accurate explanation of my structure. :-( – sdk May 04 '17 at 07:38
  • 1
    first, please provide formatted text, not pictures. and second, your link is broken;) – Vao Tsun May 04 '17 at 07:40
  • Link works for me on several devices. Added a formatted text structure. I already have the answer I needed but maybe s.o. has a better one, who knows. Hope it helps. – sdk May 04 '17 at 08:00
  • cool, thanks. I wanted to understand what hides behind "(more or less fk)", but you have your answer, so no matter. and also please read https://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 – Vao Tsun May 04 '17 at 08:07
  • I'll keep that in mind, thanks. – sdk May 04 '17 at 08:08
  • A FK says subrow values for some columns must be subrow values for other columns. "More or less" means nothing. "Child" & "parent" mean something *given* a directed relationship. You're confused about what relationships you mean when using them. Eg the relationship on tables that one references another via FK. Or that one is referenced by another via FK. (That's the relationship we mean for "parent" in a FK sense.) Also a table represents a business/application relationship on values or entities identified by values. 1:1 justifies FKs both ways--a parent & child are *also* child & parent. – philipxy May 05 '17 at 02:37
  • I wasn't as confused as it seems here but the last sentence about the 1:1 relationship, that was interesting, thanks! It may looked confusing because I didn't know how to describe my problem. Now you're mentioning that in a 1:1 relationship p & c are c & p but what I meant with "more or less" and "theoretical" is … I didn't want the deletion of `password_policy` causing a deletion of my `domain_realm`, probably having many more of these strange fk's. Because they are NOT in an equal relationship (in my head at least). Hard to explain. Thank you very much for clearing things up a bit! – sdk May 05 '17 at 06:06

1 Answers1

2

You can create a trigger after delete from domain_realm. I'm not 100% sure about the syntax in postgresql, but it should look something like this:

CREATE OR REPLACE FUNCTION update_policy() RETURNS TRIGGER AS $$
    BEGIN
            DELETE FROM password_policy WHERE password_policy_id = OLD.password_policy_id;
            IF NOT FOUND THEN RETURN NULL; END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER clean_policy
AFTER DELETE ON domain_realm
FOR EACH ROW EXECUTE PROCEDURE update_policy();
Ciprian Lipan
  • 340
  • 2
  • 9
  • I am not sure about `IF NOT FOUND THEN RETURN NULL; END IF;` (leads me to "ERROR: control reached end of trigger procedure without RETURN"), but it works exactly as I've hoped for! I only changed above to simply `RETURN NULL`. I'll research a bit further about triggers and why my workaround could be wrong but for now thank you very much! – sdk May 04 '17 at 07:33