Foreign Key Relationship Constraint... cant figure this out

[OfficeOfTheLaw]

I recently redid my schema using innoDB and created a bunch of foreign key constraints (the database is storing some loan application data). Basically, it's set up so that when an entry from the application table is removed, all the records from the other tables pointing to it are also removed.

However, I have a couple tables I can't seem to get some kind of constraint working with them. Here is the schema for the two tables:

CREATE TABLE `secured_credit` (
  `personal_id` int(10) unsigned NOT NULL default '0',
  `property_description` varchar(255) NOT NULL default '',
  `spouse` varchar(100) NOT NULL default '',
  `coowner_1` int(10) unsigned default NULL,
  `coowner_2` int(10) unsigned default NULL,
  `coowner_3` int(10) unsigned default NULL,
  `coowner_4` int(10) unsigned default NULL,
  `coowner_5` int(10) unsigned default NULL,
  PRIMARY KEY  (`personal_id`),
  KEY `personal_id` (`personal_id`).
  FOREIGN KEY (`personal_id`) REFERENCES `personal_information` (`personal_id`) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE `secured_property_coowners` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(30) default NULL,
  `lname` varchar(30) default NULL,
  `street` varchar(40) default NULL,
  `city` varchar(30) default NULL,
  `state` varchar(15) default NULL,
  `zip` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`)
) TYPE=InnoDB;

Basically, each coowner column in the secured_credit table references a row in the secured_property_coowners. How can I set this up so that when an entry in secured_credit is deleted, any of the entries referenced in the other table are removed?

Thanks.

[r937]

you cannot (at least not with mysql innodb)

what you describe is a deletion of the child table causing a deletion of the parent

that requires a trigger, and i think you can write triggers in version 5 (i'm not really sure, and i'm too lazy to look it up)

besides, what if there is a property in the secured_credit table with one or more co-owners and for some reason you want to remove that property -- you are suggesting that you want also to delete that co-owner (who might also have multiple other properties)???

to me, that sounds just plain wrong

[OfficeOfTheLaw]

besides, what if there is a property in the secured_credit table with one or more co-owners and for some reason you want to remove that property -- you are suggesting that you want also to delete that co-owner (who might also have multiple other properties)???

to me, that sounds just plain wrong

There's no domain level logic that applies to the co-owners... their purpose is only to exist as part of the loan applicant's information on their secured credit. Once the application is removed from the system, there's no need for the co-owners to exist in the databse any longer.

[r937]

and if the co-owner happens to have more than one property in the database?

[r937]

oh, wait a sec, are you saying that whenever you insert a row into secured_credit, you automatically just insert one to five rows into secured_property_coowners, with no regard to whether they exist or not?

so if i'm a co-owner on seventeen properties, i'll have seventeen rows in secured_property_coowners?

eewwww

yeah, triggers, or else when you go to delete the secured_credit row, just delete the secured_property_coowners row(s) first, using a multi-table delete, then delete the secured_credit row second

you don't need a trigger to run two DELETE statements in a row, and come to think of it, they wouldn't need transaction locking either, since if the first one succeeds and the second doesn't (the typical situation which requires locking), you haven't left the database in a state of dis-integrity (omg i can't think too well today)

finally, there is a way that you can allow ordinary relational integrity ON DELETE CASCADE to handle this situation, but since it requires that you normalize those 5 columns into a separate table, i won't mention it unless you can redesign