How can I relate this two tables?

[r937]

here is my suggestion:

CREATE TABLE restaurants
( id  INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(255) NOT NULL
, image VARCHAR(100)
);
CREATE TABLE foodtypes
( id  INTEGER NOT NULL PRIMARY KEY
, foodtype VARCHAR(37) NOT NULL
);
CREATE TABLE restaurant_foodtypes
( restaurants_id INTEGER NOT NULL
, foodtypes_id INTEGER NOT NULL 
, FOREIGN KEY ( restaurants_id ) REFERENCES restaurants ( id )
, FOREIGN KEY ( foodtypes_id ) REFERENCES foodtypes ( id )
, PRIMARY KEY ( restaurants_id, foodtypes_id )
, INDEX reversi ( foodtypes_id, restaurants_id )
);

i have changed several names in the interest of clarity

the restaurant_foodtypes table is called a many-to-many or association or relationship table

[co.ador]

I am trying to import to mysql the format of the restaurant_foodtypes table into phpmyadmin in mysql 5.1 and it throw this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY ( foodtypes_id) REFERENCES foodtypes (id)
PRIMARY KEY (`restauran' at line 12

I don't know how to structure the syntax for FOREIGN KEY and REFERENCES in this mysql version. I tried to do it like you did but it didn't work.

The mysql dump of the table restaurant_foodtypes in the mysql version in my computer dumps it as below without the FOREING KEY and REFERENCES you have add to it.


- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 22, 2009 at 06:07 AM
-- Server version: 5.1.33
-- PHP Version: 5.2.9-2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `menu`
--

-- --------------------------------------------------------

--
-- Table structure for table `restaurants_foodtypes`
--

CREATE TABLE IF NOT EXISTS `restaurants_foodtypes` (
`restaurants_id` int(1) NOT NULL,
`foodtypes_id` int(1) NOT NULL,
PRIMARY KEY (`restaurants_id`,`foodtypes_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `restaurants_foodtypes`
--