Foreign Key Problem

--000e0cdf0ee4af40560486de8971
Content-Type: text/plain; charset=ISO-8859-1

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

--000e0cdf0ee4af40560486de8971--

Re: Foreign Key Problem

--0016e646119e4859560486ded878
Content-Type: text/plain; charset=ISO-8859-1

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 ?

On Tue, May 18, 2010 at 3:44 PM, Victor Subervi 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

--0016e646119e4859560486ded878--

Re: Foreign Key Problem

--000e0cd70408c9c4fc0486e070db
Content-Type: text/plain; charset=ISO-8859-1

On Tue, May 18, 2010 at 10:06 AM, Johan De Meersman 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 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
>

--000e0cd70408c9c4fc0486e070db--

Re: Foreign Key Problem

--0014853d2058e802930486e13688
Content-Type: text/plain; charset=ISO-8859-1

On Tue, May 18, 2010 at 6:00 PM, Victor Subervi wrote:

>
>
> So apparently it didn't like my foreign key. Do I need to do something with
> the table I'm referencing or what?
> TIA.
>

Well, quickfix is to convert your tables to innoDB, starting with the
lowest-level (foreign-key only ones) first - I'm not sure what happens if
you set a referential constraint from an innodb table to a myisam table.

You can easily convert tables with "alter table *yourtable* engine=innodb;".
For the tables where you also want to add constraints and/or indices, "alter
table *yourtable* add constraint *yourconstrainthere* engine=innodb;" does
the trick in one go.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0014853d2058e802930486e13688--

Re: Foreign Key Problem

Johan De Meersman wrote:
> On Tue, May 18, 2010 at 6:00 PM, Victor Subervi wrote:
>
>>
>> So apparently it didn't like my foreign key. Do I need to do something with
>> the table I'm referencing or what?
>> TIA.
>>
>
> Well, quickfix is to convert your tables to innoDB, starting with the
> lowest-level (foreign-key only ones) first - I'm not sure what happens if
> you set a referential constraint from an innodb table to a myisam table.
>
> You can easily convert tables with "alter table *yourtable* engine=innodb;".
> For the tables where you also want to add constraints and/or indices, "alter
> table *yourtable* add constraint *yourconstrainthere* engine=innodb;" does
> the trick in one go.
>
>

Both ends of the key need to be InnoDB. The fields also need to match in
type, nullability, and collation.

For additional details about failed FK attempts, check the error details
in the SHOW INNODB STATUS report.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Foreign Key Problem

--000e0cd47ca2746f9e0486e1c843
Content-Type: text/plain; charset=ISO-8859-1

On Tue, May 18, 2010 at 1:09 PM, Shawn Green wrote:

> Johan De Meersman wrote:
>


> For additional details about failed FK attempts, check the error details in
> the SHOW INNODB STATUS report.
>

I get this:

100518 10:26:22 Error in foreign key constraint of table
seaflight/Passengers:
constraint foreign key (id) references Flights (flights_id), constraint
foreign key (id) references Customers (customer_id), name varchar(40),
weight tinyint(3)) engine=InnoDB:
Cannot resolve column name close to:
), constraint foreign key (id) references Customers (customer_id), name
varchar(40), weight tinyint(3)) engine=InnoDB

Here's my command:
create table if not exists Passengers (id int unsigned auto_increment
primary key, constraint foreign key (id) references Flights (flights_id),
constraint foreign key (id) references Customers (customer_id), name
varchar(40), weight tinyint(3)) engine=InnoDB;

I think I've got those constraints right. I'm calling the data from those
other tables, not the other way around. Please advise.
TIA,
V

--000e0cd47ca2746f9e0486e1c843--

Re: Foreign Key Problem

Victor Subervi wrote:
> On Tue, May 18, 2010 at 1:09 PM, Shawn Green > > wrote:
>
> Johan De Meersman wrote:
>
>
>
> For additional details about failed FK attempts, check the error
> details in the SHOW INNODB STATUS report.
>
>
> I get this:
>
> 100518 10:26:22 Error in foreign key constraint of table
> seaflight/Passengers:
> constraint foreign key (id) references Flights (flights_id), constraint
> foreign key (id) references Customers (customer_id), name varchar(40),
> weight tinyint(3)) engine=InnoDB:
> Cannot resolve column name close to:
> ), constraint foreign key (id) references Customers (customer_id), name
> varchar(40), weight tinyint(3)) engine=InnoDB
>
> Here's my command:
> create table if not exists Passengers (id int unsigned auto_increment
> primary key, constraint foreign key (id) references Flights
> (flights_id), constraint foreign key (id) references Customers
> (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB;
>
> I think I've got those constraints right. I'm calling the data from
> those other tables, not the other way around. Please advise.
> TIA,
> V

I may be confused but how can the ID of the Passengers table be both the
ID of the Flight they are taking and their Customer ID at the same time?

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-co nstraints.html

You may want additional ID columns in the Passengers table to point to
the parent values in those other tables.

Also,

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Foreign Key Problem

--000e0cd6ae44e3b0da0486f2eacf
Content-Type: text/plain; charset=ISO-8859-1

On Tue, May 18, 2010 at 2:23 PM, Shawn Green wrote:

> Shawn Green wrote:
> I may be confused but how can the ID of the Passengers table be both the ID
> of the Flight they are taking and their Customer ID at the same time?
>
> http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-co nstraints.html
>
> You may want additional ID columns in the Passengers table to point to the
> parent values in those other tables.
>

Please help me out here. This is what I have:

mysql> describe Customers;
+-------------+----------------------------+------+-----+--- ------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-------------+----------------------------+------+-----+--- ------+----------------+
| id | int(11) | NO | PRI | NULL |
auto_increment |

mysql> describe Flights;
+-------------+-------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+---------+-- --------------+
| id | int(11) | NO | PRI | NULL | auto_increment |

create table if not exists Passengers (id int(11) 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;

Please help me see where I'm stumbling. All the fields have the same type.
If I've got it right, "foreign key (id) references Flights (flights_id)"
means that the field "flights_id" will be created in the table Customers and
it will reference id in Flights. Trying to interchange those throws an error
indicating that flights_id doesn't exist, presumably in Flights. I'm lost,
but close to home ;) Please help.
V

--000e0cd6ae44e3b0da0486f2eacf--

Re: Foreign Key Problem

Victor Subervi wrote:
> On Tue, May 18, 2010 at 2:23 PM, Shawn Green wrote:
>
>> Shawn Green wrote:
>> I may be confused but how can the ID of the Passengers table be both the ID
>> of the Flight they are taking and their Customer ID at the same time?
>>
>> http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-co nstraints.html
>>
>> You may want additional ID columns in the Passengers table to point to the
>> parent values in those other tables.
>>
>
> Please help me out here. This is what I have:
>
> mysql> describe Customers;
> +-------------+----------------------------+------+-----+--- ------+----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +-------------+----------------------------+------+-----+--- ------+----------------+
> | id | int(11) | NO | PRI | NULL |
> auto_increment |
>
> mysql> describe Flights;
> +-------------+-------------------+------+-----+---------+-- --------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+-------------------+------+-----+---------+-- --------------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
>
> create table if not exists Passengers (id int(11) 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;
>
> Please help me see where I'm stumbling. All the fields have the same type.
> If I've got it right, "foreign key (id) references Flights (flights_id)"
> means that the field "flights_id" will be created in the table Customers and
> it will reference id in Flights. Trying to interchange those throws an error
> indicating that flights_id doesn't exist, presumably in Flights. I'm lost,
> but close to home ;) Please help.
> V
>

AH! that's your mistake. You think that creating the FK will also create
the column. That does not happen. You have to define the table
completely before you can associate the columns on this table (the child
table) with the correct column on the parent table (either Flights or
Customers).

You need to declare two more fields before you can link them through a
Foreign Key relationship to a field on another table:

CREATE TABLE PASSENGERS (
id int auto_increment
, flights_id int not null
, customer_id int not null
.... other passenger table columns here ...
, PRIMARY KEY (id)
, FOREIGN KEY (flights_id) REFERENCES Flights(id)
, FOREIGN KEY (customer_id) REFERENCES Customer(id)
) ENGINE=INNODB;

Try it that way and see if it helps.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Foreign Key Problem

--000e0cd649765c5abd0486f46ab7
Content-Type: text/plain; charset=ISO-8859-1

On Wed, May 19, 2010 at 10:59 AM, Shawn Green wrote:

> Shawn Green wrote:
>
> AH! that's your mistake. You think that creating the FK will also create
> the column. That does not happen. You have to define the table completely
> before you can associate the columns on this table (the child table) with
> the correct column on the parent table (either Flights or Customers).
>
> You need to declare two more fields before you can link them through a
> Foreign Key relationship to a field on another table:
>
> CREATE TABLE PASSENGERS (
> id int auto_increment
> , flights_id int not null
> , customer_id int not null
> ... other passenger table columns here ...
> , PRIMARY KEY (id)
> , FOREIGN KEY (flights_id) REFERENCES Flights(id)
> , FOREIGN KEY (customer_id) REFERENCES Customer(id)
> ) ENGINE=INNODB;
>
> Try it that way and see if it helps.
>
> Well, that was certainly one error, but there's another:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100519 8:46:10 Error in foreign key constraint of table
seaflight/Passengers:
foreign key (id) references Flights (flights_id), foreign key (id)
references Customers (customer_id), name varchar(40), weight tinyint(3))
engine=InnoDB:
Cannot resolve column name close to:
), foreign key (id) references Customers (customer_id), name varchar(40),
weight tinyint(3)) engine=InnoDB

Now, I've added the innodb engine to all tables and constraints on the id
columns of the foreign key tables. What else?
TIA,
V

--000e0cd649765c5abd0486f46ab7--

Re: Foreign Key Problem

Victor Subervi wrote:
> On Wed, May 19, 2010 at 10:59 AM, Shawn Green wrote:
>
>> Shawn Green wrote:
>>
>> AH! that's your mistake. You think that creating the FK will also create
>> the column. That does not happen. You have to define the table completely
>> before you can associate the columns on this table (the child table) with
>> the correct column on the parent table (either Flights or Customers).
>>
>> You need to declare two more fields before you can link them through a
>> Foreign Key relationship to a field on another table:
>>
>> CREATE TABLE PASSENGERS (
>> id int auto_increment
>> , flights_id int not null
>> , customer_id int not null
>> ... other passenger table columns here ...
>> , PRIMARY KEY (id)
>> , FOREIGN KEY (flights_id) REFERENCES Flights(id)
>> , FOREIGN KEY (customer_id) REFERENCES Customer(id)
>> ) ENGINE=INNODB;
>>
>> Try it that way and see if it helps.
>>
>> Well, that was certainly one error, but there's another:
>
> ------------------------
> LATEST FOREIGN KEY ERROR
> ------------------------
> 100519 8:46:10 Error in foreign key constraint of table
> seaflight/Passengers:
> foreign key (id) references Flights (flights_id), foreign key (id)
> references Customers (customer_id), name varchar(40), weight tinyint(3))
> engine=InnoDB:
> Cannot resolve column name close to:
> ), foreign key (id) references Customers (customer_id), name varchar(40),
> weight tinyint(3)) engine=InnoDB
>
> Now, I've added the innodb engine to all tables and constraints on the id
> columns of the foreign key tables. What else?
> TIA,
> V
>
look again closely at your FK definitions. The pattern should be

FOREIGN KEY (child_table_column) REFERENCES
parent_table(parent_table_column)

Yours appears to be something else.


--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Foreign Key Problem

--000e0cd4b11a997377048707155e
Content-Type: text/plain; charset=ISO-8859-1

On Wed, May 19, 2010 at 12:02 PM, Shawn Green wrote:

> Victor Subervi wrote:
>
>> On Wed, May 19, 2010 at 10:59 AM, Shawn Green >> >wrote:
>>
>> Shawn Green wrote:
>>
>>

> look again closely at your FK definitions. The pattern should be
>>>
>>
> FOREIGN KEY (child_table_column) REFERENCES
> parent_table(parent_table_column)
>
> Yours appears to be something else.


And indeed it was. This works:

create table if not exists Passengers (id int(11) auto_increment primary
key, flights_id int(11) not null, customer_id int(11) not null, foreign key
(id) references Flights (flights_id), foreign key (id) references Customers
(customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB;

Yay! Thanks!
V

>
>
>
> --
> Shawn Green
> MySQL Principle Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>

--000e0cd4b11a997377048707155e--

Re: Foreign Key Problem

--001485eba90083faaa04872f1e3d
Content-Type: text/plain; charset=ISO-8859-1

This is just for the sake of future googlers of this thread. The correct
mysql command is:

ursor.execute('create table if not exists Passengers (id int(11)
auto_increment primary key, flights_id int(11) not null, customer_id int(11)
not null, foreign key (flights_id) references Flights (id), foreign key
(customer_id) references Customers (id), name varchar(40), weight int, price
float(6,2)) engine=InnoDB;')

beno

--001485eba90083faaa04872f1e3d--