From: jayabharath (jbharathjgmail.com)
Date: Tue Jun 22 2010 - 11:23:57 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi Victor,

The actual problem is with the key field.

Flights.pilot_id is set to INT NOT NULL and you had specified Pilots.id to
INT NULL.

You have to change both the columns to NULL or else NOT NULL to avoid the
error.

Regards,
Jay
MySQL DBA
Datavail CORP

On Tue, Jun 22, 2010 at 7:45 PM, Victor Subervi <victorsubervigmail.com>wrote:

> Hi;
>
> mysql> alter table Flights type=InnoDB;
> Query OK, 1 row affected, 1 warning (0.01 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> mysql> alter table Flights add pilot_id int not null;
> Query OK, 1 row affected (0.01 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> mysql> alter table Flights add foreign key (pilot_id) references Pilots
> (id);
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT
> `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`))
> mysql> alter table Pilots type=InnoDB;
> Query OK, 1 row affected, 1 warning (0.01 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> mysql> alter table Flights add foreign key (pilot_id) references Pilots
> (id);
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT
> `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`))
> mysql> describe Pilots;
> +-------------+-------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+-------------+------+-----+---------+----------------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
> | first_name | varchar(20) | NO | | NULL | |
> | middle_name | varchar(20) | YES | | NULL | |
> | last_name | varchar(20) | NO | | NULL | |
> | weight | int(11) | NO | | NULL | |
> +-------------+-------------+------+-----+---------+----------------+
> 5 rows in set (0.00 sec)
>
> Please advise how to alter Flights to take the foreign key.
> TIA,
> Victor
>

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]