Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0

    Setting up db table relationships - seeking help


    I"m currently working on a side project whereby Iíve populated a mySQL database with 5 tables. I'm a total noob to DB programming and am stumped at the moment. I'm not sure how to link the tables to each other via foreign keys, especially in the presence of composite primary keys.

    I'd like to ask you for your help in determining the proper relationship from a SQL point of view. If I was to DELETE an entry in the computer_systems table, say acme_100 for example, I would want to have its entries deleted from the other tables as well.

    But first, I need to get the relationships between the tables correct.

    I'm using myPHPadmin which permits me to manually create tables and add values. It also has a SQL terminal for me to enter sQL commands. I also have access to mySQL workbench.

    I have a pic showing snippits of each of the tables (there will be more rows when I'm done). I guess because this is my first post I'm not allowed to attach pics though?

    The 5 tables are as follows:

    table: computer_system
    column 1: PC_name (PK)
    column 2: manufacturer

    table: system_params
    column 1: PC_name (PK)
    column 2: CPU (PK)
    column 3: algorithm (PK)
    column 4: pwr_consumption
    column 5: nodes_processed

    In this table, pwr_consumption and nodes_processed are dependent on the unique combination of PC_name, CPU, and algorithm which collectively I believe would be a composite primary key. PC_name would be a foreign key to the same name in the computer_system table.


    table: filter_params
    column 1: algorithm (PK)
    column 2: filter_coeffs

    OK, this is a little confusing to me, algorithms in this table needs to link to algorithms in the system_params table. I'm not sure how
    to properly do this.


    table: algorithm_params
    column 1: algorithm (PK)
    column 2: method
    column 3: lines_of_code
    column 4: num_variables
    column 5: num_authors

    same comment as previous.

    table: CPUs
    column 1: CPU (PK)
    column 2: cache
    column 3: GHz

    CPU will need to link to the system_params table as well. I'm just not sure how to do this.

    Any help will be appreciated.

    Angus
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    Code:
    ALTER TABLE system_params
    ADD CONTRAINT system_param_pc 
        FOREIGN KEY ( pc_name ) 
          REFERENCES computer_system ( pc_name )
          ON DELETE CASCADE
    that's all there is to it
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Originally Posted by r937
    that's all there is to it
    Assuming of course that the relevant tables are InnoDB!

    Also, to the OP, if posting in multiple forums, please mention that fact somewhere.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by cafelatte
    Assuming of course that the relevant tables are InnoDB!

    Also, to the OP, if posting in multiple forums, please mention that fact somewhere.

    My tables are InnoDB, so I'll give it a shot. Thanks.

    I should probably pull my post from the other forum since unfortunately, it has become overloaded with spam.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    Code:
    ALTER TABLE system_params
    ADD CONTRAINT system_param_pc 
        FOREIGN KEY ( pc_name ) 
          REFERENCES computer_system ( pc_name )
          ON DELETE CASCADE
    that's all there is to it
    Thanks. Linking the PK in computer_system to the composite PK in system_params looks pretty straightforward: pc_name to pc_name. I've now got that working.

    How would I then get the primary keys in other tables to reference the columns of the same name in system_params? For example, I need to tie the algorithm column in the algorithm_params table to the algorithm column in the system_params table. In this case I would have a PK in the former table linking to part of a composite primary key in the second.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    Originally Posted by angusy
    How would I then get the primary keys in other tables to reference the columns of the same name in system_params?
    it actually works in the other direction -- the foreign key in one table always references the primary key in another

    when you look at it that way, you will see how easy it becomes

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    it actually works in the other direction -- the foreign key in one table always references the primary key in another

    when you look at it that way, you will see how easy it becomes

    r937,

    Here is what I've attempted:

    I tried setting up CPU in the system_params table as a foreign key to CPU in the CPUs table (where it is a PK). This is despite already having set up CPU as a composite primary key (with PC and algorithms) within the system_params table.

    I also tried the reverse where CPU was the foreign key from the CPUs table pointed to CPU as a PK in the system_params table.

    When attempting to DELETE a CPU entry I can't escape this error:

    #1452 - Cannot add or update a child row: a foreign key constraint fails (`computer_system`.<result 2 when explaining filename '#sql-1404_10c'>,
    CONSTRAINT `#sql-1404_10c_ibfk_2` FOREIGN KEY (`CPU`) REFERENCES `CPUs` (`CPU`) ON DELETE CASCADE O)

    So, I'm not sure what else to try here?? Any idea if/when I'll be able to post an attachment?

    I welcome your feedback.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    you won't need an attachment, plain text is preferred

    please run a SHOW CREATE TABLE for each of the two tables you just mentioned
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    you won't need an attachment, plain text is preferred

    please run a SHOW CREATE TABLE for each of the two tables you just mentioned
    r937,

    Since the filter_params table is just matrix data there is neither a primary key nor any unique or composite keys. My mistake.

    Also I wasn't able to add any additional FK relationships out of the system_params table other than what is shown below.

    I executed the command you requested on all of my tables. Here is what I got:


    'CREATE TABLE `algorithm_params` (
    `algorithm` varchar(20) NOT NULL,
    `method` varchar(20) NOT NULL,
    `lines_of_code` int(11) NOT NULL,
    `num_variables` int(11) NOT NULL,
    `num_authors` int(11) NOT NULL,
    PRIMARY KEY (`algorithm`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1'



    'CREATE TABLE `computer_system` (
    `PC_name` varchar(20) NOT NULL,
    `manufacturer` varchar(20) NOT NULL,
    PRIMARY KEY (`PC_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1'



    'CREATE TABLE `cpus` (
    `CPU` varchar(20) NOT NULL,
    `cache` int(11) NOT NULL,
    `GHz` int(11) NOT NULL,
    PRIMARY KEY (`CPU`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1'



    'CREATE TABLE `filter_params` (
    `algorithm` varchar(20) NOT NULL,
    `filter_coeffs` float NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1'


    'CREATE TABLE `system_params` (
    `PC_name` varchar(20) NOT NULL,
    `CPU` varchar(20) NOT NULL,
    `algorithm` varchar(20) NOT NULL,
    `pwr_consumption` int(11) NOT NULL,
    `nodes_processed` int(11) NOT NULL,
    PRIMARY KEY (`PC_name`,`CPU`,`algorithm`),
    KEY `CPU` (`CPU`),
    KEY `algorithm` (`algorithm`),
    CONSTRAINT `system_params_ibfk_3` FOREIGN KEY (`PC_name`) REFERENCES `computer_system` (`PC_name`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1'
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    run this --
    Code:
    ALTER TABLE system_params
    ADD CONSTRAINT system_param_cpu
          FOREIGN KEY ( cpu ) REFERENCES cpus ( cpu )
            ON DELETE CASCADE
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    run this --
    Code:
    ALTER TABLE system_params
    ADD CONSTRAINT system_param_cpu
          FOREIGN KEY ( cpu ) REFERENCES cpus ( cpu )
            ON DELETE CASCADE
    I just issued the command. Looking at the system_params table under 'Relations' I see that column CPU has a foreign key constraint `test`.` cpus`.` CPU` ON DELETE CASCADE ON UPDATE RESTRICT

    BTW, just wondering for the ADD CONSTRAINT part shouldn't it technically be system_params_cpu ? I didn't get an error with your command statement though.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    the constraint name is just an optional name that you can assign (useful if you want to drop the constraint)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    the constraint name is just an optional name that you can assign (useful if you want to drop the constraint)
    So, I assume with the last command and the initial manual foreign key I added that the DB structure now has the CPU columns and the PC_name columns synced between their respective tables and system_params. Is this correct?

    Would I just then perform similar commands to the one you just posted for the algorithm columns? This would be to link algorithm column in the algorithm_params table to the algorithm column in the system_params table.

    I'll hold off on the filter_params table for a moment. Thanks for helping me move along. Please advise me on the next step.
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    you will notice that the code i gave you for those two foreign key constraints was remarkably similar

    go ahead and do the same for the remaining ones

    then test them -- try adding a system param for a non-existing cpu, try deleting a cpu to see if its system params also get deleted, etc.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    you will notice that the code i gave you for those two foreign key constraints was remarkably similar

    go ahead and do the same for the remaining ones

    then test them -- try adding a system param for a non-existing cpu, try deleting a cpu to see if its system params also get deleted, etc.
    I just linked the algorithm column from algorithm_params to system_params. I think this is now in order.

    So this just leaves the filter_params table. Is it even possible to link algorithm in filter_params to algorithm in system_params via foreign keys? The filter_params table doesn't have a primary key, composite key, or unique key since it is just matrix float values representing filter tap weights which may be repeated. So I'm a little confused on the linking technique.

    Once I get all the table links done, I'll try issuing DELETE command right from computer_system to see if they cascade as intended.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo