#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,566
    Rep Power
    171

    Is it really terrible not to specify any foreign keys?


    Hello;

    I was just reading Relational Integrity chapter of this book and see I should assign foreign keys to let the system know about the relationships.

    I have never set a foreign key before. Everything based on primary_key has been working just fine without assigning foreign keys.

    I don't find declaring foreign keys neccessary. Assigning primary_keys and having the column to connect to that It does what I want it to (connecting tables).

    Is there much I am missing out?

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

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    Is it really terrible not to specify any foreign keys?
    is it really terrible to have bad data in your database?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,743
    Rep Power
    9397
    Do you mean without the constraints? As in
    Code:
    otherTableID INT UNSIGNED NOT NULL
    versus
    Code:
    otherTableID INT UNSIGNED NOT NULL REFERENCES otherTable (id)
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,566
    Rep Power
    171
    Originally Posted by requinix
    Do you mean without the constraints
    For example in this query:
    Code:
    SELECT test_table_members.name       AS member,
           Sum(test_table_orders.amount) AS total
    FROM   test_table_members
           LEFT OUTER JOIN test_table_orders
                        ON test_table_orders.member_id = test_table_members.id
    GROUP  BY member
    It uses joins 2 tables together using a key but I have not assigned it as foreign key inthe create table:
    Code:
    CREATE TABLE `test_table_members`
      (
         `id`   INT(11) NOT NULL auto_increment,
         `name` VARCHAR(100) NOT NULL,
         PRIMARY KEY (`id`)
      )
    engine=myisam
    DEFAULT charset=utf8
    Code:
    CREATE TABLE `test_table_orders`
      (
         `id`        INT(11) NOT NULL auto_increment,
         `member_id` INT(11) NOT NULL,
         `amount`    INT(11) NOT NULL,
         PRIMARY KEY (`id`)
      )
    engine=myisam
    DEFAULT charset=utf8
    Is this bad database design?
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,910
    Rep Power
    1045
    Hi,

    Originally Posted by zxcvbnm
    Is this bad database design?
    Yes, because you neither enforce valid references in the "member_id" column, nor do you have an index on that column -- which is a sure way to get broken data and slow queries.

    You can do that in the sense of "MySQL didn't show any errors". Yes, MySQL lets you get away with it. But if you care about proper database design in any way, don't do it.

    Your example actually demonstrates that very well. Without a foreign key constraint on "member_id", the column can be filled with any integer. Could be -519023, could be 0, could be 99999. MySQL doesn't care. It takes whatever your application sends it, no matter if there is an order with that ID or not. MySQL doesn't even know that there's a connection between those tables.

    So you have absolutely no guarantee that an order has a corresponding member. If your application has a bug or you or somebody else fumbles with the data, you can easily end up with broken orders and no known buyer. Now what?

    If you don't have an online shop, and you just play around with your private homepage, well, then those problems might not bother you. You can simply delete the broken data, and that's it. But if you wanna get into professional web development, you'll have do more than just get the query to run somehow. "MySQL didn't show any errors" is not enough for a website with important data and lots of traffic.

    Note that the MyISAM engine doesn't support foreign keys. You can write them down, but they're just ignored. You have to use InnoDB instead.
  10. #6
  11. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,743
    Rep Power
    9397
    Jacques can be a bit... harsh sometimes. By virtue of that member_id column you do have a foreign key, but there's no enforcement of the fact that the corresponding member exists. Like he said, the member_id could be any number whatsoever. But if you've coded your application anywhere near reasonably that shouldn't happen. (Should still use an actual constraint though.)
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,910
    Rep Power
    1045
    There's a lot of things that shouldn't happen. But they do happen, and that's why people invented safety belts, door locks, condoms -- and table constraints.
  14. #8
  15. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,566
    Rep Power
    171
    I tried it to see what it looks like I ran these:
    Code:
    --
    -- Table structure for table `posts_keyword`
    --
    
    CREATE TABLE IF NOT EXISTS `posts_keyword` (
      `post_id` int(11) NOT NULL,
      `keyword_id` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `posts_keyword`
    --
    Code:
    --
    -- Table structure for table `posts`
    --
    
    CREATE TABLE IF NOT EXISTS `posts` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(100) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `post_index` (`title`)
    )
    And added the foreign key
    Code:
     ALTER TABLE posts_keyword
      ADD CONSTRAINT fk_pst_id FOREIGN KEY (post_id) REFERENCES posts (id)
    Expecting the system not allowing me to insert values in posts_keyword with ids that do not exists in posts table. But it let me. Is it because of what you mentioned above? MyISAM engine ignires foreign keys?
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,910
    Rep Power
    1045
    Originally Posted by zxcvbnm
    Is it because of what you mentioned above? MyISAM engine ignires foreign keys?
    Yes. MyISAM does not support foreign key constraints. But InnoDB does.

IMN logo majestic logo threadwatch logo seochat tools logo