Geek Answers Handbook

MySQL: unable to create table

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 got this error:

ERROR 1005 (HY000): Cannot create table fooschema.visit (errno: 121)

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

------------------------
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 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 following query to list all available restrictions:

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

I did not see any restrictions named "FK_visit_site" as a result.

The FK_visit_site constraint was the foreign key constraint for visiting the table. I threw a business card and tried to recreate it.

Is there a way I can reject this foreign key constraint even if the table associated with it does not exist?

+4
source share
2 answers

, , .

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

.

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;

PRIMARY KEY visit_id.

:

, site_id site site_id .

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

, , (INT NOT NULL),

+1

AFAIK, , , - . , FK FK_visit_site .

, , , , , .

,

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

http://www.thenoyes.com/littlenoise/?p=81

FK,

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`),
)
0

More articles:


All Articles