CODEWITHSUNDEEP
phpforeign-keysmysql
Irfan Ahmad
Irfan Ahmad

Reputation: 129

DISCARD the tablespace before IMPORT And Cannot Add Foreign Key Cons

I was re-creating a table mytab with simple command CREATE TABLE mytab(t INT PRIMARY KEY,NAME VARCHAR(100)) but it was giving me below error

Error : Tablespace for table 'database.mytab' exists. Please DISCARD the tablespace before IMPORT.

I search and found .ibd physical file on his data directory then I remove it manually and restart the service but now when I am re-executing the command its giving below error:

Error Code: 1215 Cannot add foreign key constraint

Upvotes: 0

Views: 1717

Answers (1)

Irfan Ahmad
Irfan Ahmad

Reputation: 129

to fix error code:1215 first find out foreign key based tables and drop their constraints.

find FK constraints through

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_schema='my_schema'

Drop the constraints and recreate the table and then recreate the required constraints.

Upvotes: 1

Related Questions