Add Foreign Key to existing table

372
votes

I want to add a Foreign Key to a table called "katalog".

ALTER TABLE katalog 
ADD CONSTRAINT `fk_katalog_sprache` 
FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL;

When I try to do this, I get this error message:

Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150)

Error in INNODB Status:

120405 14:02:57 Error in foreign key constraint of table mytable.#sql-7fb1_7d3a:

FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL:
Cannot resolve table name close to:
(`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL

When i use this query it works, but with wrong "on delete" action:

ALTER TABLE `katalog` 
ADD FOREIGN KEY (`Sprache` ) REFERENCES `sprache` (`ID` )

Both tables are InnoDB and both fields are "INT(11) not null". I'm using MySQL 5.1.61. Trying to fire this ALTER Query with MySQL Workbench (newest) on a MacBook Pro.

Table Create Statements:

CREATE TABLE `katalog` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`AnzahlSeiten` int(4) unsigned NOT NULL,
`Sprache` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `katalogname_uq` (`Name`)
 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC$$

CREATE TABLE `sprache` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
 `Bezeichnung` varchar(45) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Bezeichnung_UNIQUE` (`Bezeichnung`),
KEY `ix_sprache_id` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
14
mysqlforeign-keys
Since you didn't post the output of SHOW CREATE TABLE, I can only but ask - is the column name really ID, uppercased? – N.B.
Well, it's easier to spot now - katalog has int(11) unsigned. sprache doesn't have the usigned part, therefore two columns aren't the same. – N.B.
Do you mean, the both Primary fields must be same data type? – frgtv10
This is the problem with your design: first, you're referencing two auto_increment columns which is bad. Also, the MySQL manual says: Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.. Therefore, yes, similar data type and the same sign. – N.B.
I'm not referencing two auto_increment fields. katalog.Sprache (not auto) -> sprache.ID (auto) – frgtv10

14 Answers

620
votes

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);
87
votes

Simply use this query, I have tried it as per my scenario and it works well

ALTER TABLE katalog ADD FOREIGN KEY (`Sprache`) REFERENCES Sprache(`ID`);
34
votes

Simple Steps...

ALTER TABLE t_name1 ADD FOREIGN KEY (column_name) REFERENCES t_name2(column_name)
16
votes

check this link. It has helped me with errno 150: http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

On the top of my head two things come to mind.

  • Is your foreign key index a unique name in the whole database (#3 in the list)?
  • Are you trying to set the table PK to NULL on update (#5 in the list)?

I'm guessing the problem is with the set NULL on update (if my brains aren't on backwards today as they so often are...).

Edit: I missed the comments on your original post. Unsigned/not unsigned int columns maybe resolved your case. Hope my link helps someone in the future thought.

14
votes
FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL;

But your table has:

CREATE TABLE `katalog` (
`Sprache` int(11) NOT NULL,

It cant set the column Sprache to NULL because it is defined as NOT NULL.

7
votes

How to fix Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150) in mysql.

  1. alter your table and add an index to it..

    ALTER TABLE users ADD INDEX index_name (index_column)

  2. Now add the constraint 

    ALTER TABLE foreign_key_table
ADD CONSTRAINT foreign_key_name FOREIGN KEY (foreign_key_column)
REFERENCES primary_key_table (primary_key_column) ON DELETE NO ACTION
ON UPDATE CASCADE;

Note if you don't add an index it wont work.

After battling with it for about 6 hours I came up with the solution I hope this save a soul.

6
votes

MySQL will execute this query:

ALTER TABLE `db`.`table1`
ADD COLUMN `col_table2_fk` INT UNSIGNED NULL,
ADD INDEX `col_table2_fk_idx` (`col_table2_fk` ASC),
ADD CONSTRAINT `col_table2_fk1`
FOREIGN KEY (`col_table2_fk`)
REFERENCES `db`.`table2` (`table2_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

Cheers!

4
votes

When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

  1. Create index
  2. Alter table
4
votes

try all in one query

  ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
      ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);
1
votes

step 1: run this script

SET FOREIGN_KEY_CHECKS=0;

step 2: add column

ALTER TABLE mileage_unit ADD COLUMN COMPANY_ID BIGINT(20) NOT NULL

step 3: add foreign key to the added column

ALTER TABLE mileage_unit
ADD FOREIGN KEY (COMPANY_ID) REFERENCES company_mst(COMPANY_ID);

step 4: run this script

SET FOREIGN_KEY_CHECKS=1;
1
votes

The foreign key constraint must be the same data type as the primary key in the reference table and column

0
votes

this is basically happens because your tables are in two different charsets. as a example one table created in charset=utf-8 and other tables is created in CHARSET=latin1 so you want be able add foriegn key to these tables. use same charset in both tables then you will be able to add foriegn keys. error 1005 foriegn key constraint incorrectly formed can resolve from this

0
votes

i geted through the same problem. I my case the table already have data and there were key in this table that was not present in the reference table. So i had to delete this rows that disrespect the constraints and everything worked.

-1
votes
 ALTER TABLE TABLENAME ADD FOREIGN KEY (Column Name) REFERENCES TableName(column name)

Example:- 

ALTER TABLE Department ADD FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)