5

Possible Duplicate:
Foreign key constraint may cause cycles or multiple cascade paths?

I have a database of contact details that contains four primary tables:

  • Organisations
    • Departments
      • Sections
        • People

Here is a diagram (the arrows indicate foreign key constraints):

IMPORTANT: I accidentally marked the green arrow as pointing to per_PerID. It should point to per_SecID

enter image description here

I have created constraints that ensure each department record falls under an organisation, each section falls under a department, and so forth. These foreign key constraints have their action set to CASCADE, so that deleting an organisation will delete all corresponding departments etc.

The problem is that each organisation needs to have a person in charge. This translates to a foreign key constraint on the field that will contain the ID of the person in charge. Creating the constraint was easy, but when I tried to set the ON DELETE action to SET NULL, I got the following error:

Introducing FOREIGN KEY constraint ... on table ... may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

My reasoning was that cycles would only occur if I set the action to CASCADE, not SET NULL, but evidently this was incorrect.

  1. Person record deleted
  2. org_incharge_PerID set to null in corresponding organisation record
  3. No further propagation occurs. If the organisation had been deleted instead there would be a problem, since departments also refer to organisations. As things stand there shouldn't be an issue.

Is there another way to ensure referential integrity, or should I rethink my table structure, in a way that can avoid the cycles problem entirely?

What I want is for org_incharge_PerID to be constrained to the set of per_PerIDs in the persons table. Additionally, I want for the value to be set to NULL if that person is deleted.

Community
  • 1
  • 1
Asad Saeeduddin
  • 46,193
  • 6
  • 90
  • 139
  • My suggestion would be not to use CASCADE. Are you aware that if you leave ON DELETE NO ACTION, you still get referential integrity? You would just get an error when you try to delete something that would break RE. – Dave Cousineau Dec 09 '12 at 07:57
  • what is the per_secID field in your persons table? What's the action for that foreign key? – jachguate Dec 09 '12 at 07:59
  • @Sahuagin `ON DELETE NO ACTION` is what I have the organisations-persons constraint set to at the moment. Unfortunately, I need to be able to delete a person who is a head of the department without having to find the corresponding organisation and changing the value to NULL myself (this would be pretty inconvenient). Or do you mean I should use NO ACTION for the org-dep, dep-sec etc. relations? – Asad Saeeduddin Dec 09 '12 at 08:00
  • @jachguate The `per_secID` is a foreign key field that refers to the primary key in the `sections` table. A person must be a member of a section. – Asad Saeeduddin Dec 09 '12 at 08:02
  • Yeah, you could still use ON DELETE SET NULL for that one relation. Having CASCADE DELETEs everywhere is a little dangerous as well as a bit messy. I'm not sure what the official view on it is, but IMHO the use of anything other than ON DELETE NO ACTION should be minimized. – Dave Cousineau Dec 09 '12 at 08:06
  • @Sahuagin Not using `CASCADE` here would be exceptionally messy, because I would have to start cleaning up records from the bottom up. First I would have to find all departments for an organisation, then all sections for those departments, then all people in those sections. Next I would have to start deleting in reverse, deleting all people records, then all sections, then all departments, then finally the organisation. Avoiding this is sort of the point of `CASCADE`. – Asad Saeeduddin Dec 09 '12 at 08:10
  • Never mind! I was thinking it was misspelled. Sorry! – ErikE Dec 09 '12 at 08:29
  • @jachguate Nope, that has nothing to do with `SET NULL` actions, which is the key issue here. The other question pertains to multiple `CASCADE` action constraints, which understandably causes cycles. I've explained this in my question, if you take the time to read it. – Asad Saeeduddin Dec 09 '12 at 09:01
  • @Asad did you read the referenced question? for example, it says: _I need for the fields **to be set to null** if the code that is referenced is deleted_. IMHO, the answer, anyway, address your question. – jachguate Dec 09 '12 at 09:10
  • @jachguate You're right, missed that. – Asad Saeeduddin Dec 09 '12 at 09:16
  • I don't see this as an exact duplicate because while the answer "you can't get around the cycles problem with this exact table structure" is identical, there was additional question, "how would I rethink this table structure"? My answer below offers completely new, meaningful, and helpful content that has value and should be showcased as a good answer. Furthermore, my answer wouldn't really fit in the question called an exact duplicate. That seems to me to suggest that this question is worthwhile as a separate item. – ErikE Dec 10 '12 at 00:47

1 Answers1

5

Once you have run into the cycles warning, the only other way to ensure referential integrity without changing your database structure is to use a trigger for one of the constraints. The problem triggering the warning is twofold:

  1. The Sections table shares two opposite-direction foreign key constraints with the Persons table.
  2. All the four tables have a full cycle that progresses through all of them in the same direction, Person->Section->Department->Organization.

Additionally, there is an issue with the table design: the way it is constructed, a person could be in charge of a section that he is not a member of.

You can get around two these three problems by removing InCharge column from Sections and introducing another table, SectionInCharge, containing columns PerID and SecID, with a clustered index/unique constraint on SecID, and a composite foreign key to the Persons table on both columns. You will need to add a UNIQUE constraint to the two columns in the Persons table so the FK can relate to them.

Now you can set the new FK to ON DELETE CASCADE and delete away.

It is often a superior database design pattern to use the presence or absence of rows to signify something rather than to use a nullable column.

Now that I think about it, the other tables with a "person in charge" column have a similar problem in regards to allowing the assignment of someone who is not properly a member. The only way I know to solve this using FKs (which in my mind is best practice over triggers) is by propagating columns through tables and including them in composite keys. This has the unfortunate side effect of making things a little awkward, but it is not too bad as you can put the most-specific column first and join only on it when possible. So you would put OrgID in the Section table, with a composite FK to the Dept table on (DeptID, OrgID), and then put DeptID and OrgID also in the Persons table. You will then need DeptInCharge and OrgInCharge tables.

One more note about this (that's not really about solving the core problem you presented) that you might be interested in is the possibility of combining your three bottom tables into a single table, OrgUnit. Add an OrgUnitType column and a ParentOrgID column. Either add columns that will be NULL when the thing does not possess that property (as in Sections not having a fax number), or make the existing Organizations, Departments, and Sections tables subtypes of the OrgUnit (example of DB supertype/subtype). The advantage of this is that then you can refer to any one of the three types anywhere you want to: if the FK is to OrgUnit, it can be any one of them. But if it is only allowed to be a Section, then you would FK to that table instead. In fact, that is then perfect for a single OrgUnitInCharge table, with an FK to OrgUnit (OrgUnitID, OrgUnitTypeID).

I know there are some loose ends here that I haven't tied up completely into a pretty package for you, but I hope that what I've described so far is helpful.

P.S. I hope you will forgive me for adding a stylistic consideration. To me it is a lot of extra needless decoration to put a table-signifying prefix on every column in each table. I type 90-110 wpm depending on how good I feel, but I still hate typing any more than I have to. Also, I believe very strongly in keeping a high signal-to-noise ratio in my code, and for me, the prefixes exploding all over the place drastically reduce that. In SQL queries, best practice is to use table aliases and then use those aliases on each column, as in SELECT P.Name FROM dbo.Person P. To resolve the issue of multiple tables having the same column names, I have started making the typical WidgetName column in my Widget table to also be Widget. As one example of why a high signal-to-noise is so important: vrbauxCan proYou verRead artThis nouSentence advQuickly conAnd advEasily? True, the additional prefixes contain useful information, but that useful information is only a little useful, and a lot distracting. If you're designing your database from scratch now, I'd like to request, as someone who could by a strange coincidence some day end up maintaining it (stranger things have happened), that you not use those prefixes. :)

P.P.S. I like PascalCase over under_scores because it's easier to type. The underscore is on the top row and hit by the weakest finger--so it's slower and error prone.

P.P.P.S. I also prefer singular table names, but that's neither here nor there is it? I apologize in advance for hitting you up with style critique.

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • Good idea. I'll try this out. `The problem triggering the warning is of course that the Sections table has two connections to the Persons table.` This part isn't necessarily true: even if I remove the section-incharge and department-incharge fields, the warning still occurs, even though now the max number of connections between tables is 1. – Asad Saeeduddin Dec 09 '12 at 08:22
  • Thanks for the critique. I see the additional problem now. Updating. – ErikE Dec 09 '12 at 08:23
  • Thanks for selecting my answer. Do you have any further comments on my suggestions? I'd be eager to know what you finally chose and how it worked out for you. – ErikE Dec 10 '12 at 05:24
  • I accepted this answer because you put me on the right track regarding rethinking my table design, instead of trying to make the old one work. I'm taking some time off to work on a different project right now, but when I implement this I'll update you on the details. – Asad Saeeduddin Dec 11 '12 at 11:39
  • How did the project go? – ErikE Sep 04 '16 at 20:55
  • I switched to MongoDB :) – Asad Saeeduddin Sep 04 '16 at 21:26