Add Foreign Key to existing table

Question

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

Dilworth

Since you didn't post the output of SHOW CREATE TABLE, I can only but ask - is the column name really ID, uppercased? – Cyd

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

Do you mean, the both Primary fields must be same data type? – Dilworth

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

I'm not referencing two auto_increment fields. katalog.Sprache (not auto) -> sprache.ID (auto) – Dilworth

A +1 to N.B. In my case, I had a script where the first letter in the table name wasn't upper-cased in the constraint definition. – Isleana

Answer 1

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

Kleper

Reasons help me understand and remember. This is because you can't add a foreign key to an non-unsigned field, correct? – Unpremeditated

@PixMach, the answer is no. You can have signed integers as foreign keys. As N.B. noted on the question, the type and sign of the fields need to match. So if your primary key in the lookup table is UNSIGNED, then the foreign key field must also be UNSIGNED. If the primary key field is SIGNED, then foreign key field must also be signed. Think of it this way: whatever the column in the one table is defined as in a SHOW CREATE TABLE, it needs to have the same definition in the other table. – Annorah

Note that this could also be done with a single query (might be better in case of failure etc) – Il

And do not forget to derive your index column on your refrence table, if you re using phpmyadmin mysql as MariaDb as database storege i guess it work for oracle mysql as well – Peg

I had to run "SET FOREIGN_KEY_CHECKS=0;" before running the ADD CONSTRAINT command or SQL would complain "Cannot add or update a child row: a foreign key constraint fails". – Asis

Do not just run "SET FOREIGN_KEY_CHECKS=0;" if you get the error "a foreign key constraint fails", you obviously have bad data that you must fix or else you will get bigger problems down the line. – Tactile

@Erin Geyer, its been 2 years or so, but the 'Cannot add or update a child row: a foreign key constraint fails' usually happens because of data inconsistency between a newly added column which you have set as a foreign key reference to a column on another table which does not yet have the data on the column it refers to. For example, the newly added column usually contains empty or default data while the column it refers to already has data. In this scenario: Create the new column, import the data from the existing column it is to refer to, then set the new column as a foreign key reference. – Rhea

@StijndeWitt But you will lose the liberty of having a named constraint which sucks when you have to delete it because they will be autogenerated – Sherrie

For any error, read this https://mcmap.net/q/82040/-mysql-foreign-key-constraint-is-incorrectly-formed-error/6576302 – Lovegrass

Answer 2

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`);

Precipitant

You need to put a warning/disclaimer that this will create a constraint name for you. Something which will have grave consequences when dealing with database migrations when you want to modify your constraints. It's always good practice to name things. – Sherrie

For any error, read this https://mcmap.net/q/82040/-mysql-foreign-key-constraint-is-incorrectly-formed-error/6576302 – Lovegrass

Answer 3

Simple Steps...

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

Disagreeable

For any error, read this https://mcmap.net/q/82040/-mysql-foreign-key-constraint-is-incorrectly-formed-error/6576302 – Lovegrass

Answer 4

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.

Reign

Answer 5

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.

Ethnography

Answer 6

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.

Significance

Answer 7

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!

Helicoid

Answer 8

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

Kayleigh

I created index for both. When I try to run i get the same error message as before. When I disclaim the "on delete on update" part it works but with the wrong "on delete" action ;) – Dilworth

@Dilworth the most likely the tables data conflicts with "on delete". – Kayleigh

Answer 9

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

Clean

Answer 10

ALTER TABLE child_table_name ADD FOREIGN KEY (child_table_column) REFERENCES parent_table_name(parent_table_column);

child_table_name is that table in which we want to add constraint. child_table_column is that table column in which we want to add foreign key. parent table is that table from which we want to take reference. parent_table_column is column name of the parent table from which we take reference

Hendiadys

Answer 11

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;

Enchilada

Per the comments on the accepted answer in 2018, many have said do not run SET FOREIGN_KEY_CHECKS=0; blindly. If you're getting the error "Cannot add or update a child row: a foreign key constraint fails", it probably means you have db issues that need rectifying. For me, I solved it by just adding the column, setting its values manually and then setting it as the foreign key like the accepted answer. Adding a disclaimer would help. – Casilde

Answer 12

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

Raina

Answer 13

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

Lithium

Answer 14

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

Example:-

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

Loathe

Please add some explanation to your code such that others can learn from it – Heda

Answer 15

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.

Herringbone

Answer 16

Double check if the engine and charset of the both tables are the same.

If not, it will show this error.

Carliecarlile

Answer 17

I had exactly the same error message when trying to update a database I created some jears ago. I had a table holding f. Ex. products with a primary key column 'orderNr'. My intention was to add a 'version' field and extend the primary key to 'orderNr' and 'version'. When I added the 'version' to an existing table, I could easyly add a foreign key like

ALTER TABLE mytable 
ADD CONSTRAINT fk_mytable_products 
FOREIGN KEY (orderNr, version) REFERENCES products (orderNr, version)

But I could not add this key to a new table like

CREATE TABLE newtable 
...
FOREIGN KEY (orderNr, version) REFERENCES products (orderNr, version)

The foreign key columns had the same type, were both not unsigned, the tables had the same char set, the same collation and both tables created by InnoDB. The only difference was mytable existed for years and newtable was created at the moment.

In mysql workbench you can export your data to a dump file (be sure to chose 'data only'). there is a right mouse click feature for tables like 'write sql statement for create', writing the create statement for a table to a query window. When you click on 'Tables' instead of one specific table, it creates all craete statments for all tables. Unfortunateley the create statements are ordered by the alphabet, but need to be ordered in a way making sure any table being referenced in the create statement of a new table must already have been created before. When having done that you can drop your whole schema.

Then create the schema again. Run the sql script creating all your tables. Import data from the dump file.

Then I could create a new table and add a foreign key from it to one of the existing ones.

The part of the error message ...

'such columns in old tables cannot be referenced by such columns in new tables'

made me try that.

What ever the difference is, between the creation process now compared to the one before, to me it worked fine. I killed some hours with that, so maybe this might help some one else.

Ghana

For me the best way for creating new tables with indexes, foreign keys ... seems to be writing out as create statement for a existing table in mysql workbench and modify it to create your new table. Now, why this comment: The statement created contains 'SET ... and COLLATE ...' at the end, which can really cause trouble concerning foreign keys. Even though all my tables were created with collation utf8, the create statement for the new table could only be executed after I removed 'COLLATE utf8' from the statement ! – Ghana