SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru OfficeOfTheLaw's Avatar
    Join Date
    Apr 2004
    Location
    Quincy
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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;
    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.

    James Carr, Software Engineer


    assertEquals(newXPJob, you.ask(officeOfTheLaw));

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru OfficeOfTheLaw's Avatar
    Join Date
    Apr 2004
    Location
    Quincy
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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.

    James Carr, Software Engineer


    assertEquals(newXPJob, you.ask(officeOfTheLaw));

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •