Can´t INSERT values with Foreign Key in MySQL

Question

I have a MySQL (5.1.42 on OsX) running. I added a Foreign Key with this sql statement:

ALTER TABLE `Portal`.`Mitarbeiter_2_BlackBerry` ADD CONSTRAINT `fk_Blackberry` FOREIGN KEY (`id` ) REFERENCES `Portal`.`Blackberry` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `fk_Blackberry` (`id` ASC)

But when i try to insert values in that table with this sql statement:

INSERT INTO Mitarbeiter_2_BlackBerry SET uebergabeAm = '2009-12-01 13:00:00', fk_Blackberry = (SELECT id FROM Blackberry WHERE id = '1')

I got the following Error: Error Code: 1054 Unknown column 'fk_BlackBerry' in 'field list'

Anybody an idea what could be wrong? Thanks for any hint :-) Lars.

David M · Accepted Answer · 2010-01-07 12:27:01Z

You need to put the value in the column id not the constraint fk_Blackerry. And if you know the value is 1, just insert 1- you don't need the subquery.

Nitin Midha · Accepted Answer · 2010-01-07 12:31:44Z

As per the Syntax Which you you can refer from here.

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

fk_Blackerry is Symbol, not the column. Column on which foreign key constraint is id, So the updated query should be

INSERT INTO Mitarbeiter_2_BlackBerry SET uebergabeAm = '2009-12-01 13:00:00', id = (SELECT id FROM Blackberry WHERE id = '1')

Collectives™ on Stack Overflow

Can´t INSERT values with Foreign Key in MySQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related