List:General Discussion« Previous MessageNext Message »
From:Victor Subervi Date:May 18 2010 4:00pm
Subject:Re: Foreign Key Problem
View as plain text  
On Tue, May 18, 2010 at 10:06 AM, Johan De Meersman <vegivamp@stripped>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
> <victorsubervi@stripped>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
>

Thread
Foreign Key ProblemVictor Subervi18 May
  • Re: Foreign Key ProblemJohan De Meersman18 May
    • Re: Foreign Key ProblemVictor Subervi18 May
      • Re: Foreign Key ProblemJohan De Meersman18 May
        • Re: Foreign Key ProblemShawn Green18 May
          • Re: Foreign Key ProblemVictor Subervi18 May
            • Re: Foreign Key ProblemShawn Green18 May
              • Re: Foreign Key ProblemVictor Subervi19 May
                • Re: Foreign Key ProblemShawn Green19 May
                  • Re: Foreign Key ProblemVictor Subervi19 May
                    • Re: Foreign Key ProblemShawn Green19 May
                      • Re: Foreign Key ProblemVictor Subervi20 May
                        • Re: Foreign Key ProblemVictor Subervi22 May