SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble creating foreign key references with MySQL/CocoaSQL

    Hi,

    I have two tables, questions and questionnaires. In questions I want a field that references a questionnaire, but CocoaSQL (frontend for mysql) will not accept the syntax used in the Agile Web Dev book. I don't have the example here (don't have internet at home, now at uni), but it's something like
    constraint fk_questions_questionnaires questions(questionnaire_id) references questionnaire(id)

    Is this a problem of mine, of mysql or something else?
    I thinking about using postgresql instead, but I need to use a (preferably) free hosting site to test it on.

    So basically, my question is, how do I create a one-to-many relationship in my database?

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The foreign key database stuff is optional. You can create the relationship in the model without it. As long as the tables have the appropriate columns, you can work with it.

    Someone will eventually get you some tips on that query, but don't let it stop you from experimenting.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe your version of MySQL, which version are you running?
    Alternatively have you set the table type to InnoDB, the standard is MyISAM and foreign keys cannot be set in this type of table.

  4. #4
    SitePoint Member
    Join Date
    Dec 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stamppot
    Hi,

    I have two tables, questions and questionnaires. In questions I want a field that references a questionnaire, but CocoaSQL (frontend for mysql) will not accept the syntax used in the Agile Web Dev book. I don't have the example here (don't have internet at home, now at uni), but it's something like
    constraint fk_questions_questionnaires questions(questionnaire_id) references questionnaire(id)
    I copied the Agile books suggest and switched to writing the SQl create statements in a text file and then invoking it via the terminal window. I still use CocoaSQL to browse the contents but for rapid destroy and development of my table structure I can't beat this method.

    _Tony

  5. #5
    SitePoint Member
    Join Date
    Jun 2005
    Location
    Eastbourne, UK
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The examples in the Agile Web Development with Rails are a little out of date now, if you don't know about them already have a look at migrations - http://wiki.rubyonrails.com/rails/pa...dingMigrations

    They make it extremely easy to update your database as you are developing your application and for "migrating" other instances of your application to the current database structure with a simple "rake migrate" call. You can even use them to set up default or test data for your applications.

  6. #6
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Somehow, mysql (4.(0|1).16 didn't accept the InnoDB keyword, I think. I even think it's using Isam now...

    I also used the method of creating a text file and then insert it into mysql, but it was in mysql that the syntax failed.

    I'll take a look at migrations too. Thanks.

  7. #7
    SitePoint Member
    Join Date
    Jun 2005
    Location
    Eastbourne, UK
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, in that case make sure InnoDB support is urned on in your my.cnf file. Certainly on windows installs it's not turned on by default.


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
  •