MySQL: Can't create table

Question

I tried to create a table in MySQL using the CREATE TABLE statement below:

CREATE TABLE `visit` (
  `visit_id` int(11) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`visit_id`),
  CONSTRAINT `FK_visit_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`site_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I received this error:

ERROR 1005 (HY000): Can't create table 'fooschema.visit' (errno: 121)

I used SHOW ENGINE INNODB STATUS command. This is the error message:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
140222  7:03:17 Error in foreign key constraint creation for table `fooschema`.`visit`.
A foreign key constraint of name `fooschema/FK_visit_site`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Then, I used the query below to list all available constraints:

select *
from information_schema.table_constraints
where constraint_schema = 'fooschema'

I didn't see any constraint with name 'FK_visit_site' in the result.

The FK_visit_site constraint was a foreign key constraint of table visit. I dropped the visit table and attempted to recreate it.

Is there a way I can drop this foreign key constraint even when the table it was associated to doesn't exist?

yes I could. But I would really like to use the old name. Using the old name is kind of important. — toanong
– toanong, Commented Feb 22, 2014 at 12:34
What does select * from information_schema.table_constraints where constraint_name = 'FK_visit_site' return` — user330315
– user330315, Commented Feb 22, 2014 at 12:35
too bad about the name. I would expect that the foreign key must be in there somewheres though.. — Nanne
– Nanne, Commented Feb 22, 2014 at 12:36
select * from information_schema.table_constraints where constraint_name = 'FK_visit_site' returned Empty set. — toanong
– toanong, Commented Feb 22, 2014 at 12:38

echo_Me · Accepted Answer · 2014-02-22 13:21:05Z

1

your foreign key already exist , so either drop existed foreign key or rename your second key.

 ALTER TABLE `site` DROP FOREIGN KEY `FK_visit_site`;

or rename to other new one.

CREATE TABLE `visit` (
 `visit_id` int(11) NOT NULL PRIMARY KEY,
 `site_id` int(11) NOT NULL,
CONSTRAINT `FK_visit_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`site_id`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Added PRIMARY KEY to the visit_id line.

Note:

make sure that site_id in the site table have exact same datatype of site_id in visit table.

like that

  `site_id` int(11) DEFAULT NULL  --//in the `site` table

The two keys you're coupling must have the exact same datatype ( INT NOT NULL), even signedness

edited Feb 22, 2014 at 13:21

answered Feb 22, 2014 at 12:38

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

toanong Over a year ago

Having explicit constraint name is important in my case because I might need to drop it programmatically later.

toanong Over a year ago

Tried the edited version. Here's site table: CREATE TABLE site ( site_id int(11) NOT NULL, location_id int(11) DEFAULT NULL, site_name varchar(50) DEFAULT NULL, PRIMARY KEY (site_id), CONSTRAINT FK_site_location FOREIGN KEY (location_id) REFERENCES location (location_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

echo_Me Over a year ago

try drop the FK_visit_site and then create it again in your table , just to make sure if it ecist.

echo_Me Over a year ago

dude your site_id datatype is not same in both tables. i have edited my answer craeted table.try it

toanong Over a year ago

I tried that. Here's the error message: ERROR 1146 (42S02): Table 'fooschema.visit' doesn't exist

|

Rahul · Accepted Answer · 2014-02-22 12:49:29Z

0

AFAIK, you will get this error when you're trying to add a constraint with a name that's already used somewhere else. Means, in your case FK FK_visit_site had already been used before.

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.

You can run the below query to find out the same

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

Taken from the post here http://www.thenoyes.com/littlenoise/?p=81

Try using a different name for your FK like

CREATE TABLE `visit` (
  `visit_id` int(11) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`visit_id`),
  CONSTRAINT `FK_visit_site_New` FOREIGN KEY (`site_id`) 
  REFERENCES `site` (`site_id`),
)

answered Feb 22, 2014 at 12:49

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

4 Comments

toanong Over a year ago

The constraint name is not in the result of the query you mentioned. I would have to keep the old name. My concern is if the name was used somewhere, where is it? Why can't I find it?

Rahul Over a year ago

I saw your comment above that you aborted add constraint. are you still using the same session? Try running the query I have mentioned in a different session and see if you find it out.

toanong Over a year ago

Just tried the query in a new session. Still didn't see it. The foreign key was added to the visit table. Now that the visit table doesn't even exist. How does that foreign key still exist?

RichieHH Over a year ago

Did you find a solution to this. I have exactly the same situation. The FK is not in the schema but it wont create it.

Collectives™ on Stack Overflow

MySQL: Can't create table

2 Answers 2

10 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related