Foreign Key Problem

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

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

--000e0cd71f664f046d04899f0d11--

Re: Foreign Key Problem

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

Problem solved. I tried everything that *should* have worked and didn't.
Then I just wiped the test database and started with everything *fixed* (all
engine=innodb, all keys of same type, etc.) and it all worked.
V

--000e0cd254d451f22d0489a0a2a2--

Re: Foreign Key Problem

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

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 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
>

--0016e640d01024cbd90489a0d979--

Re: Foreign Key Problem

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

On Tue, Jun 22, 2010 at 11:53 AM, jayabharath wrote:

> 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.
>

Thanks.
V

--000e0cd254d41f05410489a232e4--