2

I am trying to create a database for 2 Tables like:

Table1 : tb1-id(pk) | tb2-id | other

Table2 : tb2-id(pk) | other

FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE CASCADE

Table pseudo-code:

CREATE TABLE Table1 (
tbl1-id        INT PRIMARY KEY
, tbl2-id      INT FOREIGN KEY REFERENCES Table2(tbl2-id) ON DELETE CASCADE
...
)

Table2:

CREATE TABLE Table 2(
, tbl2-id      INT PRIMARY KEY
...
)

There can be 2 records in Table1 that point to a single record in Table2. If we DELETE 1 of the 2 records pointing to Table2 from Table1 MySQL would also DELETE the referenced record from Table2. I want the referenced record to be deleted only when both the referencing records in Table1 are deleted.

Is there any other MySQL method/constraint that can fulfill the above requirement.

Improve this question
3
  • 2
    You're using the terms backwards to common usage: a "child" is dependent on a "parent." For example, a customer is typically a parent to its children in the orders table, and order headers are parents to their order line-item children. Referential integrity prevents orphans (children without parents), not DINKs. Your question may be clearer if written in terms of "master" and "detail" records.
    – Jon of All Trades
    Jul 20, 2012 at 15:22
  • thanks jon i would update it in some other common terms. In above scenario - In a school record parent's record is always dependent to children records so i used it directly as my problem. i would update it with common terms.
    – Abhishek Sachan
    Jul 20, 2012 at 17:09
  • You should know that MySQL doesn't support inline FK references. "Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications."
    – Mike Sherrill 'Cat Recall'
    Feb 8, 2013 at 0:20

1 Answer 1

Reset to default
1

Reading your question literally, "if we delete 1 of 2 records pointing to Table2 from Table1 would also delete referenced record from Table2", you just need an ordinary ON DELETE CASCADE, as you've shown: Table2 references Table1, and deleting a record in Table1 causes all related records in Table2 to be deleted, even if those records are being pointed to by other records in Table1.

However, if your goal is instead to delete from Table2 when there are no more associated records in Table1, whereas there are normally two such records, you'll need something more elaborate.

I think better in concrete terms, so based on your pre-edit question, I'll call Table1 Guardians (because "parents" can be confusing for SQL-heads when they're actually the child half of a relationship) and Table2 Students (similarly, avoiding the word "children"). The goal is to ensure that any students that do not have any guardians get deleted.

If polygamy and divorce are unheard on, one option would be to have two NULLable fields in Students, FatherID and MotherID (or Parent1 and Parent2, to allow for step-parents, gay marriage, etc.). Set these to reference Guardians with ON DELETE SET NULL, and add an ON UPDATE trigger to Students to delete a record if both fields become NULL. If Bob's father is deleted but he still has a mother, he's OK, but if his mother is deleted the poor orphan gets wiped from the database. This assumes that a CASCADE UPDATE event triggers triggers, I don't know that for a fact.

A more robust solution would be to create a third table relating Guardians to Students. It would include fields GuardianID, StudentID, EffectiveDate (these three making up a candidate key), StopDate, and maybe fields to indicate the rights of the guardian; perhaps only one has legal custody, and the other should not be allowed to remove the student from school grounds. This would allow for death or divorce: just set the old record's StopDate. Insert a new record if the remaining guardian ever remarries. You could create an ON UPDATE trigger and an ON DELETE trigger on this third table, checking to see if there are now any students which lack active guardians.

However, before putting too much engineering into this, consider: do you really need to cascade? Keep the referential integrity, of course, but just enforce deletion logic in your business logic layer. For that matter, does it even make sense to delete students? If you're asked how many students took a specific class in 2011, but you've deleted some because they've subsequently dis-enrolled, you'll have an undercount.

Improve this answer
1
  • I agree with you @jon on not deleting records. Only developers can delete a record from database no other accounts have rights to delete. there is no requirement of considering DINKs and divorced. the only point is can we delete a referenced record after deleting all referencing records. I can also resolve this problem with PHP. But if its possible with MySQL, it would remove some boundaries. If its not practically possible with MySQL i would have go with PHP way.. :(
    – Abhishek Sachan
    Jul 21, 2012 at 1:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.