[Solved-4 Solutions] error 1005 Can't create table (errno 121)
Error Description:
Error 1005 Can't create table (errno 121)
Solution 1:
We will get this message if we're trying to add a constraint with a name that's already used somewhere else
To check constraints use the following SQL query:
SELECT
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = 'FOREIGN KEY'
ANDtable_schema = DATABASE()
ORDERBY
constraint_name;click below button to copy the code. By - mysql tutorial - team
Solution 2:
Foreign Key Constraint Names Have to be Unique Within a Database
- If the table we're trying to create includes a foreign key constraint, and we've provided our own name for that constraint, remember that it must be unique within the database.
- We wasn’t aware of that. we have changed foreign key constraint names according to the following schema which appears to be used by Ruby on Rails applications, too:
<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>_fkclick below button to copy the code. By - mysql tutorial - team
Solution 3:
There is no need to name our constraints, but if we do, then that is the name that must be unique within each schema. The error is telling us that we already have constraints with the same name elsewhere.
To see where, We can Show Engine Innodb Status;
------------------------
LATEST FOREIGNKEY ERROR
------------------------
13012919:45:00 Error inforeignkeyconstraint creation fortable`test`.`baz`.
A foreignkeyconstraintof name `test`.`foo_id`
alreadyexists.click below button to copy the code. By - mysql tutorial - team
But in this case, we don't need to look that up, since it's apparent from the script that we are reusing the same constraint names in multiple table definitions.
FOREIGNKEY(`Comune`)
REFERENCES`PROGETTO`.`PAESE`(`Comune`)
ONDELETE NO ACTION
ONUPDATECASCADE)click below button to copy the code. By - mysql tutorial - team
We should not have a problem. we will find that InnoDB will generate names for our constraints, like PAESE_ibfk_1, PAESE_ibfk_2, etc.
- we can continue to declare the names of our constraints, remembering that whatever comes after the keyword CONSTRAINT has to be unique within each schema.
- "If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically."
Solution 4:
- We faced this error (errno 121) but it was caused by mysql-created intermediate tables that had been orphaned, preventing me from altering a table even though no such constraint name existed across any of my tables.
- At some point, my MySQL had crashed or failed to cleanup an intermediate table (table name starting with a #sql-) which ended up presenting me with an error such as: Can't create table '#sql-' (errno 121) when trying to run an ALTER TABLE with certain constraint names.
SELECT*FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE'%#sql%';click below button to copy the code. By - mysql tutorial - team
find .-iname'#*'click below button to copy the code. By - mysql tutorial - team
After discovering the filename, such as #sql-9ad_15.frm, we was able to drop that orphaned table in MySQL:
USEmyschema;
DROPTABLE`#mysql50##sql-9ad_15`;click below button to copy the code. By - mysql tutorial - team
After doing so, we was then able to successfully run my ALTER TABLE.