Can't create a mysql table: foreign key constraint issue

Question

I deleted a sql table (without key constraint check) and now i can't regenerate from sql query or import again.

creation query:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE TABLE `customer_entity` (
  `entity_id` int(10) UNSIGNED NOT NULL COMMENT 'Entity Id',
  `website_id` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Website Id',
  `email` varchar(255) DEFAULT NULL COMMENT 'Email',
  `group_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
  `store_id` smallint(5) UNSIGNED DEFAULT '0' COMMENT 'Store Id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
  `is_active` smallint(5) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Is Active',
  `disable_auto_group_change` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Disable automatic group change based on VAT ID',
  `created_in` varchar(255) DEFAULT NULL COMMENT 'Created From',
  `prefix` varchar(40) DEFAULT NULL COMMENT 'Prefix',
  `firstname` varchar(255) DEFAULT NULL COMMENT 'First Name',
  `middlename` varchar(255) DEFAULT NULL COMMENT 'Middle Name/Initial',
  `lastname` varchar(255) DEFAULT NULL COMMENT 'Last Name',
  `suffix` varchar(40) DEFAULT NULL COMMENT 'Suffix',
  `dob` date DEFAULT NULL COMMENT 'Date of Birth',
  `password_hash` varchar(128) DEFAULT NULL COMMENT 'Password_hash',
  `rp_token` varchar(128) DEFAULT NULL COMMENT 'Reset password token',
  `rp_token_created_at` datetime DEFAULT NULL COMMENT 'Reset password token creation time',
  `default_billing` int(10) UNSIGNED DEFAULT NULL COMMENT 'Default Billing Address',
  `default_shipping` int(10) UNSIGNED DEFAULT NULL COMMENT 'Default Shipping Address',
  `taxvat` varchar(50) DEFAULT NULL COMMENT 'Tax/VAT Number',
  `confirmation` varchar(64) DEFAULT NULL COMMENT 'Is Confirmed',
  `gender` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Gender',
  `failures_num` smallint(6) DEFAULT '0' COMMENT 'Failure Number',
  `first_failure` timestamp NULL DEFAULT NULL COMMENT 'First Failure',
  `lock_expires` timestamp NULL DEFAULT NULL COMMENT 'Lock Expiration Date'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity';

ALTER TABLE `customer_entity`
  ADD PRIMARY KEY (`entity_id`),
  ADD UNIQUE KEY `CUSTOMER_ENTITY_EMAIL_WEBSITE_ID` (`email`,`website_id`),
  ADD KEY `CUSTOMER_ENTITY_STORE_ID` (`store_id`),
  ADD KEY `CUSTOMER_ENTITY_WEBSITE_ID` (`website_id`),
  ADD KEY `CUSTOMER_ENTITY_FIRSTNAME` (`firstname`),
  ADD KEY `CUSTOMER_ENTITY_LASTNAME` (`lastname`);


ALTER TABLE `customer_entity`
  MODIFY `entity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity Id';


ALTER TABLE `customer_entity`
  ADD CONSTRAINT `CUSTOMER_ENTITY_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL,
  ADD CONSTRAINT `CUSTOMER_ENTITY_WEBSITE_ID_STORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `store_website` (`website_id`) ON DELETE SET NULL;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Error catched:

#1215 - Unable to add the referential integrity constraint (external key constraint)

I also tried to use SET FOREIGN_KEY_CHECKS = 0; but I did not succeed. I tried to import the sql table again but without success. In the creation query there are no key foreign constraints, so I can't figure out why query generates error.

The table is called "customer_entity.sql" on Magento 2 database.

Check `SHOW ENGINE INNODB STATUS`, it will include more details about the most recent foreign key error. Like which table, the specific constraint that didn't work, etc. — Bill Karwin, Feb 07 '19 at 16:20
That looks suspicious that the table has no `PRIMARY KEY` and no indexes at all. Is that really a Magento table? That's unusual and probably a matter of concern. — Bill Karwin, Feb 07 '19 at 16:24
this is a query made from mysql ( sql table export ). i deleted all the key constraint definitions to check if can solve the problem in this way. I don't know why no primary key, maybe i delete also the primary key definition. — Alfredo Lanzetta, Feb 07 '19 at 18:14
Did you also delete some `FOREIGN KEY` definitions? Please understand it's really hard to help you when you are presenting code that is substantially different from the code exhibiting the error. — Bill Karwin, Feb 07 '19 at 18:47

score 1 · Answer 1 · answered Feb 07 '19 at 16:08

1

Is the foreign key constraint on the other table? If so, you might try removing it, creating this table, and then adding the constraint back in.

answered Feb 07 '19 at 16:08

Hopper

146
6

maybe there are some key constraint on other tables, but the error don't notify which tables. In Magento lot of tables are linked with foreign key, so i prefer a solution where i can load only the table. – Alfredo Lanzetta Feb 07 '19 at 16:19

score 0 · Accepted Answer · answered Feb 08 '19 at 07:44

I found the solution to my problem.

I changed my creation query, i renamed the table from "customer_entity" to "customer_entity2".

CREATE TABLE `customer_entity2` (
/* ..etc */

ALTER TABLE `customer_entity2`
/* ..etc */

So when i run the creation query mysql not checks all the key constraints. Then i rename the table into "customer_entity" and the trick is done.

Is a too easy solution but i done it one minute ago and it works fine. Thank you all

Can't create a mysql table: foreign key constraint issue

2 Answers2