0

these are my create table sql queries i have these 2 query to make a table but i get Foreign key constraint is incorrectly formed

    CREATE TABLE IF NOT EXISTS `checks` (
   `ckeck_id` int(255) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
   `check_cost` int(255) NOT NULL,
    `check_payment_way` text CHARACTER SET utf8 NOT NULL, 
   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `check_explanation` text CHARACTER SET utf8,
    `operator_id` int(255) UNIQUE NOT NULL,
    `customer_id` int(255) UNIQUE NOT NULL, 
   `is_checked` int(1) NOT NULL DEFAULT '1',
    FOREIGN KEY (`customer_id`) REFERENCES `customers`(`customer_id`),
    FOREIGN KEY (`operator_id`) REFERENCES `operator`(`operator_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE IF NOT EXISTS `customers` (
  `customer_id` int(255) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 DEFAULT 'بی نام',
  `subscribe_code` varchar(255) CHARACTER SET latin1 NOT NULL,
  `phone` varchar(30) NOT NULL,
  `adress` text CHARACTER SET utf8 NOT NULL,
  `food_resive_way` text CHARACTER SET utf8 NOT NULL,
  `operator_id` int(255) UNIQUE NOT NULL,
  `orders` text CHARACTER SET latin1 NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_factored` int(11) NOT NULL DEFAULT '1',
  `is_send_for_chef` int(11) NOT NULL DEFAULT '1',
  FOREIGN KEY (operator_id) REFERENCES operator(operator_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE IF NOT EXISTS `operator` (
  `operator_id` int(255) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` text CHARACTER SET utf8 NOT NULL,
  `personal_id` text CHARACTER SET utf8 NOT NULL,
  `phone` text,
  `sex` text CHARACTER SET utf8,
  `age` int(255) DEFAULT NULL,
  `adress` text CHARACTER SET utf8,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

how can i fix it?

proton newfake
  • 1
  • Why make `customer_id` unique in `checks`? This means not more than one record per customer in `checks`, is this what you want? – GMB Jan 05 '20 at 22:57
  • Your code is basically fine. You just need to declare the tables in the right order: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1b07189100dca3263fea9754918a8bd6. – Gordon Linoff Jan 05 '20 at 22:58
  • @GMB no but i was just try everything for it and for this i unique it – proton newfake Jan 05 '20 at 22:59
  • it works fine, as @GordonLinoff pointed, if create the tables in proper order: http://sqlfiddle.com/#!9/8dbb9d – Ricardo Pontual Jan 05 '20 at 23:03
  • @RicardoPontual yes its for table orders but somewhere else i reference to table1 from table2 and at same time i reference table2 from table1 for this i cant create right order should i use alter or is there any way? – proton newfake Jan 05 '20 at 23:06
  • yes you can create the foreign keys later, with `alter table`, but your script worked perfect on SQLFiddle – Ricardo Pontual Jan 05 '20 at 23:08

0 Answers0