SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
Hybrid View
-
Feb 27, 2006, 09:50 #1
- 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?
-
Feb 27, 2006, 17:53 #2
- 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?
-
Feb 28, 2006, 01:32 #3
- 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.
-
Feb 28, 2006, 07:18 #4
- Join Date
- Dec 2005
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by stamppot
_Tony
-
Mar 1, 2006, 03:45 #5
- 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.
-
Mar 2, 2006, 05:58 #6
- 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.
-
Mar 4, 2006, 10:16 #7
- 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