-1

I had already created the database and all tables with foreign key constraints, but I had a column in parent table which was unique and was part of composite key, so I had to drop all foreign key constraints from all child tables and then dropped the unique constraint on parent table.

Now when I am adding foreign key constraint back to all child tables, it gives error: foreign key constraint is incorrectly formed, although I have checked both my parent and child table columns and they are of same type using same engine, unicode and all. But still this is thrown.

When I checked the table structure of child table, it has an index defined for the foreign key and the column is child column foreign key, so I tried after removing it but still error.

Query

ALTER TABLE `child_table` ADD CONSTRAINT FOREIGN KEY `fk_parent_table_child_table_column_name` (child_table_column_name) 
REFERENCES `parent_table`(parent_table_column_name) ON DELETE RESTRICT ON UPDATE NO ACTION;

both columns are VARCHAR PRIMARY KEY and NOT NULL

If more information is required please ask.

EDIT

So far I have tried to dropped the index, dropped the PRIMARY KEY CONSTRAINT from child table and dropping the column then adding it back with VARCHAR(14), NOT NULL, still no success.

EDIT 2

Also tried to add the child column in primary key and then tried to apply foreign key constraint, no success so far.

ateebahmed
  • 183
  • 5
  • 19
  • Did you checked https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error ? – Ravi Jan 27 '18 at 17:42
  • Yes I already did and tried several solutions but none worked, and I don't want to make my column a unique since I did all this because of it – ateebahmed Jan 27 '18 at 17:49
  • 1
    could you show exact column definition for both table ? – Ravi Jan 27 '18 at 17:54
  • I have already mentioned it – ateebahmed Jan 27 '18 at 17:56
  • 1
    I said **exact**, I don't think your column would be just `varchar`. Just copy paste here – Ravi Jan 27 '18 at 17:56
  • its `VARCHAR(14)` in both tables but the 14 in definition is meant to show this many characters on console it has nothing to do with datatype as most people think – ateebahmed Jan 27 '18 at 17:58
  • Please edit clarifications into posts, not comments. Don't edit via addendums, make the best possible current presentation. Please read & act on [mcve]. That inclues cut & pasteable code plus input & output & explanation. Also read hits googling your error message including error number(s) with & without your particular names/code. See [ask]. PS If a unique column set is part of a composite key then you could & should have declared the column set to be the key. Only when a composite FK references a proper superset of a PK/UNIQUE do you also have to declare the superset as PK/UNIQUE. – philipxy Jan 28 '18 at 21:29
  • I thought I already had explained enough to understand my question. – ateebahmed Jan 29 '18 at 17:52

1 Answers1

0

Since no one answered and I couldn't find a solution to my problem I had to drop the child foreign keys and then remove the parent composite key so as to remove the parent column which was VARCHAR(14) and then again create foreign keys with new primary key.

ateebahmed
  • 183
  • 5
  • 19