On Tue, May 18, 2010 at 10:06 AM, Johan De Meersman <vegiv...@tuxera.be>wrote:
> You're not specifying an engine, and the default is MyISAM, which doesn't > support foreign keys and will likely silently ignore requests for them. Can > you confirm that you've changed the default engine to InnoDB ? Got me. No, it wasn't and I'm new to this. Set up my.cnf like this: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] skip-external-locking max_connections=200 read_buffer_size=1M sort_buffer_size=1M log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size=256M innodb_additional_mem_pool_size=20M # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=64M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 and restarted mysqld. Then this: mysql> create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; ERROR 1005 (HY000): Can't create table './seaflight/Passengers.frm' (errno: 150) So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA, V > > > On Tue, May 18, 2010 at 3:44 PM, Victor Subervi > <victorsube...@gmail.com>wrote: > >> Hi; >> mysql> create table if not exists Passengers (id int unsigned >> auto_increment >> primary key, foreign key (id) references Flights (flights_id), foreign key >> (id) references Customers (customer_id), name varchar(40), weight >> tinyint(3)); >> Query OK, 0 rows affected (0.00 sec) >> >> mysql> select c.first_name, c.middle_name, c.last_name, c.suffix, >> c.discount, p.flights_id from Customers c join Passengers p on >> c.id=p.customer_id >> where flights_id=1; >> ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' >> mysql> describe Passengers; >> +--------+------------------+------+-----+---------+----------------+ >> | Field | Type | Null | Key | Default | Extra | >> +--------+------------------+------+-----+---------+----------------+ >> | id | int(10) unsigned | NO | PRI | NULL | auto_increment | >> | name | varchar(40) | YES | | NULL | | >> | weight | tinyint(3) | YES | | NULL | | >> +--------+------------------+------+-----+---------+----------------+ >> 3 rows in set (0.01 sec) >> >> So, why didn't the foreign key get created? It exists as a primary key in >> Customers. Please advise. >> TIA, >> Victor >> > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel >
