0

I do

ALTER TABLE bdt.auth 
    ADD CONSTRAINT `depositor no.` 
      FOREIGN KEY ( `depositor no.` ) 
         REFERENCES bdt.depositors( `depositor no.` ) 
            ON DELETE CASCADE 
            ON UPDATE CASCADE

I get

Can't create table 'bdt.#sql-881_140' (errno: 121)

Fields are identical There is no defined constraints:

SELECT
   constraint_name,
   table_name
FROM
   information_schema.table_constraints
WHERE
   constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
   constraint_name;

Result

Empty set (0.01 sec)

1

The error is that you're trying to use a constraint that is already identified.

SELECT
   constraint_name,
   table_name
FROM
   information_schema.table_constraints
WHERE
   constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
   constraint_name;

Should give you the constraints you've already established. Also, you can use SHOW ENGINE INNODB STATUS or SHOW INNODB STATUS after you get the error to get the logs and more information.

  • That constraint is not identified, so you're wrong. – Zhigalin Jul 14 '14 at 19:25
  • The result of the query you cited above is Empty set (0.01 sec) – Zhigalin Jul 14 '14 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.