SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    constraint advice please.

    Hi,

    In one of my (many-to-many), tables, I have set up two constraints. I need to make sure that if a business is deleted from the Db, that the record is deleted. I need to make sure that if a administator (login-person) is removed from the system, that the record is deleted.

    Currently I cannot delete or update a parent record, without MySQL throwing an error #1217.

    I think I should have the constraint set up to ON DELETE CASCADE (as highlighted below) and I can do that but, am I correct that that is how it should be done?

    bazz

    Code:
    CREATE TABLE IF NOT EXISTS login_businesses
    ( login_id INT NOT NULL
    , business_id INT NOT NULL
    , PRIMARY KEY (login_id,business_id)
    , KEY secondary_ix (business_id,login_id)
    , KEY login_id_ix (login_id)
    , CONSTRAINT loginbusinesses_login_fk 
        FOREIGN KEY (login_id) 
          REFERENCES login (login_id) ON DELETE CASCADE
    , CONSTRAINT login_businessdetails_fk 
        FOREIGN KEY (business_id) 
          REFERENCES business_details (business_id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    bazz

  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)
    if login_businesses is the many-to-many table between login and business_details, then you're defining the FKs correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes it is as you describe. However, I am finding that when I try to delete business_details that i get the following error:

    #1217 - Cannot delete or update a parent row: a foreign key constraint fails
    No other table has an FK to business_details.


    Edit:


    Maybe I shouldn't use both constraints? Perhaps I should only have the constraint for 'business_details' so that even if an admin is 'removed' ( set to inactive), all their records will remain for the purposes of amendment tracking?

    bazz

  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)
    could you please do another SHOW CREATE TABLE, on both login_businesses and business_details
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup

    Code:
    CREATE TABLE IF NOT EXISTS business_details 
    ( business_id int NOT NULL auto_increment
    , business_type_id int NOT NULL
    , tic varchar (64) NOT NULL
    , grp_name varchar(64) NOT NULL
    , premises varchar(64) NOT NULL
    , business varchar(64) NOT NULL
    , active binary(1) NOT NULL default '0'
    , meta_keywords text NOT NULL
    , meta_content text NOT NULL
    , page_title varchar(255) NOT NULL
    , PRIMARY KEY  (business_id)
    , KEY business_type_ix (business_type_id)
    , KEY tic_ix (tic)
    , CONSTRAINT businessdetails_businesstype_fk 
        FOREIGN KEY (business_type_id) 
          REFERENCES business_type (business_type_id)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE IF NOT EXISTS login_businesses
    ( login_id INT NOT NULL
    , business_id INT NOT NULL
    , PRIMARY KEY (login_id,business_id)
    , KEY secondary_ix (business_id,login_id)
    , KEY login_id_ix (login_id)
    , CONSTRAINT loginbusinesses_login_fk 
        FOREIGN KEY (login_id) 
          REFERENCES login (login_id) 
    , CONSTRAINT login_businessdetails_fk 
        FOREIGN KEY (business_id) 
          REFERENCES business_details (business_id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    bazz

  6. #6
    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)
    those look okay, no idea why you're getting the error

    however, they look like scripts -- are you sure you actually ran the SHOW commands?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    they aren't SHOW commands. I am trying to get them for you in either phpMyAdmin or heidisql. no success yet.

    Code:
    CREATE TABLE `business_details` (
      `business_id` int(11) NOT NULL auto_increment,
      `business_type_id` int(11) NOT NULL,
      `tic` varchar(64) NOT NULL,
      `grp_name` varchar(64) NOT NULL,
      `premises` varchar(64) NOT NULL,
      `business` varchar(64) NOT NULL,
      `active` binary(1) NOT NULL default '0',
      `meta_keywords` text NOT NULL,
      `meta_content` text NOT NULL,
      `page_title` varchar(255) NOT NULL,
      PRIMARY KEY  (`business_id`),
      KEY `business_type_ix` (`business_type_id`),
      KEY `tic_ix` (`tic`),
      CONSTRAINT `businessdetails_businesstype_fk` FOREIGN KEY (`business_type_id`) REFERENCES `business_type` (`business_type_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    Code:
    CREATE TABLE `login_businesses` (
      `login_id` int(11) NOT NULL,
      `business_id` int(11) NOT NULL,
      PRIMARY KEY  (`login_id`,`business_id`),
      KEY `secondary_ix` (`business_id`,`login_id`),
      KEY `login_id_ix` (`login_id`),
      CONSTRAINT `loginbusinesses_login_fk` FOREIGN KEY (`login_id`) REFERENCES `login` (`login_id`),
      CONSTRAINT `login_businessdetails_fk` FOREIGN KEY (`business_id`) REFERENCES `business_details` (`business_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    not sure why it has added int(11) to the first two columns. they should just be 'INT'

    Are backticks necessary?

    bazz

  8. #8
    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)
    go to the SQL window and run this query:
    Code:
    SHOW CREATE TABLE login_businesses
    pretty straightforward, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe we overlapped then? I posted the results above.

    bazz

  10. #10
    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)
    that looks fine

    try deleting a business again
    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
  •