Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Nov 2009
    Location
    Florida United States
    Posts
    105
    Thanks
    6
    Thanked 2 Times in 2 Posts

    Relational database foreign key

    I am new to trying to link databases together, so help me out if you can please.

    I need to make the id in this table a foreign key to the primary key id in my other table called gig_user. I searched google and came up with my engine would have to be an innodb. Is the correct? Please advise and thanks in advance.
    Code:
    CREATE TABLE `gig_profile` (
      `id` int(5) NOT NULL,
      `band_logo` varchar(75) NOT NULL,
      `genre` int(2) NOT NULL,
      `bio` text NOT NULL,
      `equipment` int(1) NOT NULL,
      `link` varchar(200) NOT NULL,
      `youtube` varchar(300) NOT NULL,
      `reference` varchar(300) NOT NULL,
      `email` varchar(100) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Last edited by calebandchels; 12-08-2011 at 08:58 PM.
    New Coder!!!!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,340 Times in 4,306 Posts
    Code:
    CREATE TABLE `gig_profile` (
      `id` int(5) NOT NULL REFERENCES gig_user(id),
      `band_logo` varchar(75) NOT NULL,
      `genre` int(2) NOT NULL,
      `bio` text NOT NULL,
      `equipment` int(1) NOT NULL,
      `link` varchar(200) NOT NULL,
      `youtube` varchar(300) NOT NULL,
      `reference` varchar(300) NOT NULL,
      `email` varchar(100) NOT NULL
    )
    *********

    The engine doesn't have to be INNODB, but MyISAM will not *ENFORCE* referential integrity. That is, if you put a number into the id field of this table that doesn't exist in the gig_user table, MyISAM won't care. INNODB will. If referential integrity is important to you, then yes, you should use INNODB.

    And as for googling: Why? All fo this is clearly laid out in the basic MySQL docs.

    http://dev.mysql.com/doc/refman/5.5/en/index.html
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    calebandchels (12-08-2011)

  • #3
    Regular Coder
    Join Date
    Nov 2009
    Location
    Florida United States
    Posts
    105
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post


    And as for googling: Why? All fo this is clearly laid out in the basic MySQL docs.

    http://dev.mysql.com/doc/refman/5.5/en/index.html
    Because I am fairly new to this that site just seems to be overwhelming when compared to the explanations given on others.
    New Coder!!!!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,340 Times in 4,306 Posts
    FWIW, the only reason to use MyISAM with MySQL is if you have tons of data and you need the fastest possible access to it. If your DB is under, say, a couple of gigabytes total size, I would stick with INNODB. (I might stick with it even in bigger DBs, but then it becomes more a matter of weighing the tradeoffs. For smaller DBs, the performance difference isn't enough to matter, and INNODB's advantages make it by far the better choice.

    Yeah, I know the MySQL docs can take some getting used to, but they are more complete than most docs for open source products and really do have all the info if you dig a bit. In the long run, if you can take a bit of time to read through them, you'll be better off. In the short run...I feel your pain.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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