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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ALTER TABLE TABLENAME ADD FOREIGN KEY  Column Name  REFERENCES TableName column name    Example -   ALTER TABLE Department ADD FOREIGN KEY  EmployeeId  REFERENCES Employee EmployeeId

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

How to fix Error Code  1005  Can t create table  mytable  sql-7fb1 7d3a   errno  150  in mysql    alter your table and add an index to it    ALTER TABLE users ADD INDEX index name  index column   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

User · Answer

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

User · Answer

When you add a foreign key constraint to a table using ALTER TABLE  remember to create the required indexes first    Create index  Alter table

User · Answer

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

User · Answer

Simple Steps     ALTER TABLE t name1 ADD FOREIGN KEY  column name  REFERENCES t name2 column name

User · Answer

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

[mysql] Add Foreign Key to existing table

Examples related to mysql

Examples related to foreign-keys