Trouble creating foreign key references with MySQL/CocoaSQL

[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)

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?

[samsm]

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.

[rossriley]

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.

[rarepleasures]

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

[b0rdslide]

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.

[stamppot]

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.

[b0rdslide]

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.