SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Jan 25, 2005, 09:01 #1
Foreign Key Relationship Constraint... cant figure this out
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:
Code: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;
Thanks.
-
Jan 25, 2005, 11:19 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 25, 2005, 11:28 #3
Originally Posted by r937
-
Jan 25, 2005, 11:44 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
and if the co-owner happens to have more than one property in the database?
-
Jan 25, 2005, 13:01 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
Bookmarks