Sql import error

[davy_yg]

Hello,

When I try to import this sql into phpmyadmin I am getting this error message:

Query SQL:

 CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
    PRIMARY KEY (`customerNumber`),
   KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
   CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1

     MySQL menyatakan: Dokumentasi
 #1005 - Can't create table `classicmodels`.`customers` (errno: 150 "Foreign key constraint is incorrectly formed") (Rincian…)

and I don't understand what's wrong?

mysqlsampledatabase.sql

    CREATE DATABASE /*!32312 IF NOT EXISTS*/`classicmodels` /*!40100 DEFAULT CHARACTER SET latin1 */;

    USE `classicmodels`;

    /*Table structure for table `customers` */

    DROP TABLE IF EXISTS `customers`;

    CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
      PRIMARY KEY (`customerNumber`),
      KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
      CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `customers` */

  insert  into `customers`(`customerNumber`,`customerName`,`contactLastName`,`contactFirstName`,`phone`,`addressLine1`,`addressLine2`,`city`,`state`,`postalCode`,`country`,`salesRepEmployeeNumber`,`creditLimit`) values 

    (103,'Atelier graphique','Schmitt','Carine ','40.32.2555','54, rue Royale',NULL,'Nantes',NULL,'44000','France',1370,'21000.00'),

    (112,'Signal Gift Stores','King','Jean','7025551838','8489 Strong St.',NULL,'Las Vegas','NV','83030','USA',1166,'71800.00'),

    (114,'Australian Collectors, Co.','Ferguson','Peter','03 9520 4555','636 St Kilda Road','Level 3','Melbourne','Victoria','3004','Australia',1611,'117300.00'),

    (119,'La Rochelle Gifts','Labrune','Janine ','40.67.8555','67, rue des Cinquante Otages',NULL,'Nantes',NULL,'44000','France',1370,'118200.00'),

    (121,'Baane Mini Imports','Bergulfsen','Jonas ','07-98 9555','Erling Skakkes gate 78',NULL,'Stavern',NULL,'4110','Norway',1504,'81700.00'),

    (124,'Mini Gifts Distributors Ltd.','Nelson','Susan','4155551450','5677 Strong St.',NULL,'San Rafael','CA','97562','USA',1165,'210500.00'),

[AndrisP]

Show your reference table structure employees

[davy_yg]

Here

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employeeNumber` int(11) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `officeCode` varchar(10) NOT NULL,
  `reportsTo` int(11) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  PRIMARY KEY (`employeeNumber`),
  KEY `reportsTo` (`reportsTo`),
  KEY `officeCode` (`officeCode`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
  CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `employees` */

insert  into `employees`(`employeeNumber`,`lastName`,`firstName`,`extension`,`email`,`officeCode`,`reportsTo`,`jobTitle`) values 

(1002,'Murphy','Diane','x5800','[e-mail]','1',NULL,'President'),

(1056,'Patterson','Mary','x4611','[e-mail]','1',1002,'VP Sales'),

[AssertNull]

Syntax seems fine. However I assume that you created and populated the tables in the following order?

1. offices
2. employees
3. customers

employees has a foreign key constraint in offices, so offices must be created before employees. customers has a foreign key constraint in employees, so employees must be created before customers.

That's for the table creations. For the data insertions, I see a customer whose sale was handled by employee 1370, which is a foreign key. I see no employee record with that employee number. Unless there is an employee record with that employee number inserted PRIOR to that customer record being inserted, the customer record insert will fail due to a failed foreign key constraint.

But that's nor your error. Your error was that the customers table could not be created. As mentioned, employees must be created before customers. Your SQL suggests that customers was created first. It can't be.

If this is indeed the error, then you should be having one of two reactions...

1. Wow. I knew that. I can't believe I missed that.
2. I didn't know that.

If your answer is number 1, correct the error and drive on. If your answer is number 2, pause this project and learn about foreign keys.

[benanamen]

The answer is simple. You just need to disable the foreign key checks first, run your querys, then turn the foreign key checks back on.

SET FOREIGN_KEY_CHECKS=0;
//Run Querys
SET FOREIGN_KEY_CHECKS=1;

[AssertNull]

The answer is simple. You just need to disable the foreign key checks first, run your querys, then turn the foreign key checks back on.

I would agree if the OP had phrased the question as "I'm an SQL expert and the only one who uses this database and I know for certain that the data is right. How can I reload a guaranteed perfect database from scratch without dealing with these foreign key errors wasting my time?"

I don't think that is the scenario here. Presumably the foreign key check is there for a reason. If the OP isn't familiar with that reason or with foreign keys in general, it's better to not turn off the errors and checking because he may have to deal with them later. I think that's true in any programming language. When you get experience, you know what warnings and errors you need to fix and what you can work around. Till you get to that point, it's best to not ignore warnings and errors.

[benanamen]

@AssertNull, Your talking about two different things, table creation and data insertion, both of which can each have their own contraint issues. The OP's first post clearly has to do with creating tables, not inserting data. Depending on the DB structure, it could even be a requirement to turn off the foreign key checks in order to create all the tables.

The OP should do just as I said with the foreign key check for creating the tables. If there happens to be any tables that have more then one foreign keyed table you will never be able to create the tables in a sequential order.

Now inserting the data, that is a different story, and would in fact have to be inserted in a certain table order as you said with the key check on. Once the tables are created, the Key contraints will immediatly make known if there is a data integrity problem when the OP inserts the data in the order as you listed. (offices, employees, customers ). But again, depending on the DB structure, you may not be able to insert data in a sequential manner, same as table creation.

If your unsure of the data integrity you can always run a Stored Procedure to check that all the data meets all the constraints.

OP has only referenced three tables. If you have a DB with "hundreds" of tables you are not going to try and recreate the DB and data one table at a time.

[AssertNull]

I suppose we'll have to agree to disagree. By the way, I don't disagree with anything you've said on the technical side. My disagreement is purely from the point of view that I think your suggestion is too advanced for an SQL noob, which the OP clearly is, and can lead to bad habits if introduced too early. That database looked familiar. I googled it. It's one of the main tutorial databases for MySQL.

http://www.mysqltutorial.org/mysql-sample-database.aspx
https://github.com/ronaldbradford/schema/blob/master/classicmodels.sql
https://bugs.mysql.com/file.php?id=24173&bug_id=82036&text=1

Note that the foreign key checks are indeed turned off temporarily in the links above, just like you suggested. OK, fine. Who am I to argue with the tutorial writers? I also imagine that the tutorial is step by step and originally didn't include foreign keys. The second link would seem to bear this out. But I don't see this very crucial line in the OP's code...

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Perhaps the OP thought incorrectly that this was strictly a comment line since it was surrounded with /* and */ and omitted it, not realizing the importance of the exclamation point and the digits. If indeed that is true, there is the error and the OP should read up on MySQL comments.

At any rate, I believe that the error message is clear to anyone who understands how foreign key constraints work, so I assume that the OP needs to learn or at least brush up on that topic before moving on if he intends to use them.

[benanamen]

Good catch on the tutorial DB. Would have been nice if the OP had mentioned it came from there. He left out the conditional comments and created his own problem.

[davy_yg]

I am getting the database from this link:

http://www.mysqltutorial.org/mysql-sample-database.aspx

Here is the table order:

customers
employees
offices
orderdetails
orders
productlines
products

I wonder why I cannot import the sql database correctly.