Question 213839 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql code give me foreign key is incorrectly formed

I want to create a small database but I get everytime an error:

here is mysql code:

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer` varchar(25) NOT NULL DEFAULT '',
  `address1` varchar(100) DEFAULT NULL,
  `city` varchar(25) NOT NULL DEFAULT '',
  `postcode` varchar(25) NOT NULL DEFAULT '',
  `country` varchar(25) NOT NULL DEFAULT '',
  `amount` varchar(11) NOT NULL DEFAULT '',
  `status` varchar(10) NOT NULL DEFAULT '',
  `deleted` varchar(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `last_modified` datetime DEFAULT NULL,
  `customers_id` int(11) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT orders_customers_id_fk FOREIGN KEY (customers_id) 
     REFERENCES customers (id)  ON DELETE SET NULL);
  
LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;


/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table customers
# ------------------------------------------------------------

DROP TABLE IF EXISTS `customers`;

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(25) NOT NULL DEFAULT '',
  `name` varchar(50) NOT NULL DEFAULT '',
  `surname` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(50) NOT NULL DEFAULT '',
  `password` char(255) NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I tried syntax of foreign key code but with the same error I don’t know why?

I get this error :

#1005 - Can't create table `management`.`orders` (errno: 150 "Foreign key constraint is incorrectly formed")
Tags:

2

Answers


  1. 0

    To declare a foreign key constraint the table to which this key is defined must already exist.

    In this case you’re trying to link to a table that doesn’t exist, so you’ll need to re-order the CREATE TABLE statements accordingly.

    Login or Signup to reply.
  2. 0

    You must to create the customers table first, so swap the tables sqls
    and then remove NOT NULL from customers_id because you had ON DELETE SET NULL to be:

    ...
`customers_id` int(11),
....

    and that it will working!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search