ERROR Error 1005 Can t create table errno 121

Question

I have troubles with forward engineering my MySQL database into WAMP server   I was going to post an image of the schema but as this is my first post I can t   Below is the executed script    use aquaticstar   SET  OLD UNIQUE CHECKS   UNIQUE CHECKS  UNIQUE CHECKS 0  SET  OLD FOREIGN KEY CHECKS   FOREIGN KEY CHECKS  FOREIGN KEY CHECKS 0  SET  OLD SQL MODE   SQL MODE  SQL MODE  TRADITIONAL ALLOW INVALID DATES    -- ----------------------------------------------------- -- Table  Students  -- ----------------------------------------------------- DROP TABLE IF EXISTS  Students     CREATE  TABLE IF NOT EXISTS  Students       id  VARCHAR 10  NOT NULL      studentName  VARCHAR 45  NOT NULL      gender  CHAR NOT NULL      birthDate  DATETIME NOT NULL      mNo  VARCHAR 10  NOT NULL      contactName  VARCHAR 45  NOT NULL      contactEmail  VARCHAR 45  NOT NULL      contactPhone  INT 10  NOT NULL      startDate  DATETIME NOT NULL      remarks  VARCHAR 200  NULL     PRIMARY KEY   id     ENGINE   InnoDB   -- ----------------------------------------------------- -- Table  Waiting List  -- ----------------------------------------------------- DROP TABLE IF EXISTS  Waiting List     CREATE  TABLE IF NOT EXISTS  Waiting List       wait id  VARCHAR 5  NOT NULL      name  VARCHAR 45  NULL      contactName  VARCHAR 45  NULL      contactPhone  INT 10  NULL      contactEmail  VARCHAR 45  NULL      status  CHAR NULL      remarks  VARCHAR 200  NULL     PRIMARY KEY   wait id     ENGINE   InnoDB   -- ----------------------------------------------------- -- Table  Schedule  -- ----------------------------------------------------- DROP TABLE IF EXISTS  Schedule     CREATE  TABLE IF NOT EXISTS  Schedule       lesson id  VARCHAR 10  NOT NULL      day  VARCHAR 3  NOT NULL      branch  VARCHAR 30  NOT NULL      level  VARCHAR 30  NOT NULL      time  TIME NOT NULL      ae  VARCHAR 45  NOT NULL     PRIMARY KEY   lesson id     ENGINE   InnoDB   -- ----------------------------------------------------- -- Table  Link  -- ----------------------------------------------------- DROP TABLE IF EXISTS  Link     CREATE  TABLE IF NOT EXISTS  Link       link id  VARCHAR 10  NOT NULL      id  VARCHAR 10  NOT NULL      lesson id  VARCHAR 10  NOT NULL     PRIMARY KEY   link id       INDEX  id idx    id  ASC      INDEX  lesson id idx    lesson id  ASC      CONSTRAINT  id      FOREIGN KEY   id        REFERENCES  Students    id        ON DELETE NO ACTION     ON UPDATE NO ACTION    CONSTRAINT  lesson id      FOREIGN KEY   lesson id        REFERENCES  Schedule    lesson id        ON DELETE NO ACTION     ON UPDATE NO ACTION  ENGINE   InnoDB   -- ----------------------------------------------------- -- Table  Attendance  -- ----------------------------------------------------- DROP TABLE IF EXISTS  Attendance     CREATE  TABLE IF NOT EXISTS  Attendance       date  DATETIME NOT NULL      attendance  VARCHAR 5  NOT NULL      link id  VARCHAR 10  NOT NULL     INDEX  link id idx    link id  ASC      CONSTRAINT  link id      FOREIGN KEY   link id        REFERENCES  Link    link id        ON DELETE NO ACTION     ON UPDATE NO ACTION  ENGINE   InnoDB    SET SQL MODE  OLD SQL MODE  SET FOREIGN KEY CHECKS  OLD FOREIGN KEY CHECKS  SET UNIQUE CHECKS  OLD UNIQUE CHECKS   -- ----------------------------------------------------- -- Data for table  Students  -- ----------------------------------------------------- START TRANSACTION  INSERT INTO  Students    id    studentName    gender    birthDate    mNo    contactName    contactEmail    contactPhone    startDate    remarks   VALUES   s001    Sam Khew    m    12 12 1991    nm    May Khew    may gmail com   0198829387   12 07 2011   NULL   INSERT INTO  Students    id    studentName    gender    birthDate    mNo    contactName    contactEmail    contactPhone    startDate    remarks   VALUES   s002    Joe Biden    m    13 03 2003    nm    Layla Biden    layla gmail com   0199283763   14 05 2011   NULL   INSERT INTO  Students    id    studentName    gender    birthDate    mNo    contactName    contactEmail    contactPhone    startDate    remarks   VALUES   s003    Bob Builder    m    14 02 2002    LK920K    Mama Builder    mama yahoo com   0167728376   29 02 2012   NULL   INSERT INTO  Students    id    studentName    gender    birthDate    mNo    contactName    contactEmail    contactPhone    startDate    remarks   VALUES   s004    Kenny Koh    m    18 02 1999    MM992    Lisa Koh    lk hotmail com   0123160231   19 01 2012   NULL   INSERT INTO  Students    id    studentName    gender    birthDate    mNo    contactName    contactEmail    contactPhone    startDate    remarks   VALUES   s005    Jane Doe    f    29 09 1999    nm    Jackie Doe    jackied gmail com   0127736254   02 03 2012   NULL   INSERT INTO  Students    id    studentName    gender    birthDate    mNo    contactName    contactEmail    contactPhone    startDate    remarks   VALUES   s006    Lola Lai    f    02 05 2004    nm    Mark Lai    mark gmail com   0198827365   11 09 2011   NULL    COMMIT   -- ----------------------------------------------------- -- Data for table  Schedule  -- ----------------------------------------------------- START TRANSACTION  INSERT INTO  Schedule    lesson id    day    branch    level    time    ae   VALUES   sat1 s4    Sat    Sunway    basic    4pm    Aini    INSERT INTO  Schedule    lesson id    day    branch    level    time    ae   VALUES   sat1 s5    Sat    Sunway    basic    5pm    Aini    INSERT INTO  Schedule    lesson id    day    branch    level    time    ae   VALUES   sat1 s6    Sat    Sunway    basic    6pm    Aini    INSERT INTO  Schedule    lesson id    day    branch    level    time    ae   VALUES   sat2 s4    Sat    Sunway    advance    4pm    Nina    INSERT INTO  Schedule    lesson id    day    branch    level    time    ae   VALUES   sat2 s5    Sat    Sunway    advance    5pm    Nina    INSERT INTO  Schedule    lesson id    day    branch    level    time    ae   VALUES   sat3 s6    Sat    Sunway    pre-comp    6pm    Marcus     COMMIT   -- ----------------------------------------------------- -- Data for table  Link  -- ----------------------------------------------------- START TRANSACTION  INSERT INTO  Link    link id    id    lesson id   VALUES   L001    s001    sat1 s4    INSERT INTO  Link    link id    id    lesson id   VALUES   L002    s002    sat1 s5    INSERT INTO  Link    link id    id    lesson id   VALUES   L003    s003    sat1 s6    INSERT INTO  Link    link id    id    lesson id   VALUES   L004    s004    sat2 s4    INSERT INTO  Link    link id    id    lesson id   VALUES   L005    s005    sat1 s5     COMMIT   -- ----------------------------------------------------- -- Data for table  Attendance  -- ----------------------------------------------------- START TRANSACTION  INSERT INTO  Attendance    date    attendance    link id   VALUES   26 9 2012    1   NULL    COMMIT    But then I get this error   Executing SQL script in server ERROR  Error 1005  Can t create table  aquaticstar link   errno  121    I can t figure out why  Can anyone help me

User · Answer

Foreign Key Constraint Names Have to be Unique Within a Database

Both @Dorvalla’s answer and this blog post mentioned above pointed me into the right direction to fix the problem for myself; quoting from the latter:

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database.

I wasn’t aware of that. I have changed my 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>_fk

For the OP’s table this would be Link_lession_id_fk, for example.

User · Answer

If you want to fix quickly  Forward Engineer again and check  Generate DROP SCHEMA  option and proceed    I assume the database doesn t contain data  so dropping it won t affect

User · Answer

Something I noticed was that I had  other database  and  Other Database  in my databases  That caused this problem as I actually had same reference in other database which caused this mysterious error

User · Answer

mysql gt  SHOW ENGINE INNODB STATUS    But in my case only this way could help  1  Make backup of current DB 2  Drop DB  not all tables  but DB  3  Create DB  check that you still have previleges  4  Restore DB from backup

User · Answer

You can login to mysql and type  mysql gt  SHOW INNODB STATUS G   You will have all the output and you should have a better idea of what the error is

User · Answer

I 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   According to the docs at http   dev mysql com doc refman 5 7 en innodb-troubleshooting-datadict html   you can search for these orphan tables with   SELECT   FROM INFORMATION SCHEMA INNODB SYS TABLES WHERE NAME LIKE    sql      The version I was working with was 5 1  but the above command only works on versions    5 6  manual is incorrect about it working for 5 5 or earlier  because INNODB SYS TABLES does not exist in such versions   I was able to find the orphaned temporary table  which did not match the one named in the message  by searching my mysql data directory in command line   find   -iname        After discovering the filename  such as  sql-9ad 15 frm  I was able to drop that orphaned table in MySQL   USE myschema  DROP TABLE   mysql50  sql-9ad 15     After doing so  I was then able to successfully run my ALTER TABLE   For completeness  as per the MySQL documentation linked   the  mysql50  prefix tells MySQL to ignore file name safe encoding introduced in MySQL 5 1

User · Answer

If you have a foreign key definition in some table and the name of the foreign key is used elsewhere as another foreign key you will have this error

User · Answer

I searched quickly for you  and it brought me here  I quote      You will get this message if you 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  AND table schema   DATABASE   ORDER BY     constraint name    Look for more information there  or try to see where the error occurs  Looks like a problem with a foreign key to me

[mysql] ERROR: Error 1005: Can't create table (errno: 121)

Foreign Key Constraint Names Have to be Unique Within a Database

Examples related to mysql