从SQL DUMP导入数据库模式

I have the following database schema that has a bunch of tables and foreign keys, when i try to import the sql dump i keep getting the following errors.

我有以下数据库架构,有一堆表和外键,当我尝试导入sql转储时,我不断收到以下错误。

Can't create table errno 150

I understand that it is trying to create tables with dependencies of tables that are not created yet but i don't understand how to import the schema without butchering out all of the foreign keys and then re-creating them per the answers given on Stack and google.

我知道它正在尝试创建具有尚未创建的表的依赖关系的表,但我不明白如何导入模式而不会删除所有外键,然后根据Stack上给出的答案重新创建它们。谷歌。

There has to be an easier way, what do big companies do that have hundreds of tables?

必须有一个更简单的方法,那些拥有数百张桌子的大公司会做些什么呢?

I have the sql statements below and any suggestions would be appreciated. Thanks

我有下面的sql语句,任何建议将不胜感激。谢谢

#
# Encoding: Unicode (UTF-8)
#


DROP TABLE IF EXISTS `contact_interest`;
DROP TABLE IF EXISTS `contact_seeking`;
DROP TABLE IF EXISTS `interests`;
DROP TABLE IF EXISTS `job_current`;
DROP TABLE IF EXISTS `job_desired`;
DROP TABLE IF EXISTS `job_listings`;
DROP TABLE IF EXISTS `my_contacts`;
DROP TABLE IF EXISTS `profession`;
DROP TABLE IF EXISTS `seeking`;
DROP TABLE IF EXISTS `status`;
DROP TABLE IF EXISTS `zip_code`;


CREATE TABLE `contact_interest` (
  `contact_id` int(10) unsigned NOT NULL,
  `interest_id` int(10) unsigned NOT NULL,
  KEY `mycontacts_contactinterest_fk` (`contact_id`),
  KEY `interests_contactinterest_fk` (`interest_id`),
  CONSTRAINT `mycontacts_contactinterest_fk` FOREIGN KEY (`contact_id`) REFERENCES `my_contacts` (`contact_id`),
  CONSTRAINT `interests_contactinterest_fk` FOREIGN KEY (`interest_id`) REFERENCES `interests` (`interest_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `contact_seeking` (
  `contact_id` int(10) unsigned NOT NULL,
  `seeking_id` int(10) unsigned NOT NULL,
  KEY `contactid_contactseeking_fk` (`contact_id`),
  KEY `seeking_contactseeking_fk` (`seeking_id`),
  CONSTRAINT `contactid_contactseeking_fk` FOREIGN KEY (`contact_id`) REFERENCES `my_contacts` (`contact_id`),
  CONSTRAINT `seeking_contactseeking_fk` FOREIGN KEY (`seeking_id`) REFERENCES `seeking` (`seeking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `interests` (
  `interest_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `interest` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`interest_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;


CREATE TABLE `job_current` (
  `contact_id` int(10) unsigned NOT NULL,
  `title` varchar(20) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  KEY `mycontacts_jobcurrent_fk` (`contact_id`),
  CONSTRAINT `mycontacts_jobcurrent_fk` FOREIGN KEY (`contact_id`) REFERENCES `my_contacts` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `job_desired` (
  `contact_id` int(10) unsigned NOT NULL,
  `title` varchar(20) DEFAULT NULL,
  `salary_low` decimal(8,2) DEFAULT NULL,
  `salary_high` decimal(8,2) DEFAULT NULL,
  `available` date DEFAULT NULL,
  `years_exp` int(11) DEFAULT NULL,
  KEY `mycontacts_jobdesired_fk` (`contact_id`),
  CONSTRAINT `mycontacts_jobdesired_fk` FOREIGN KEY (`contact_id`) REFERENCES `my_contacts` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `job_listings` (
  `job_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `zip_code` char(5) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;


CREATE TABLE `my_contacts` (
  `contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `phone` char(10) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `prof_id` int(11) unsigned NOT NULL,
  `status_id` int(10) unsigned NOT NULL,
  `zip_code` char(5) DEFAULT NULL,
  PRIMARY KEY (`contact_id`),
  KEY `profession_mycontacts_fk` (`prof_id`),
  KEY `zipcode_mycontacts_fk` (`zip_code`),
  KEY `status_my_contacts_fk` (`status_id`),
  CONSTRAINT `profession_mycontacts_fk` FOREIGN KEY (`prof_id`) REFERENCES `profession` (`prof_id`),
  CONSTRAINT `status_my_contacts_fk` FOREIGN KEY (`status_id`) REFERENCES `status` (`status_id`),
  CONSTRAINT `zipcode_mycontacts_fk` FOREIGN KEY (`zip_code`) REFERENCES `zip_code` (`zip_code`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;


CREATE TABLE `profession` (
  `prof_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `profession` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`prof_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;


CREATE TABLE `seeking` (
  `seeking_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seeking` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`seeking_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;


CREATE TABLE `status` (
  `status_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;


CREATE TABLE `zip_code` (
  `zip_code` char(5) NOT NULL DEFAULT '',
  `city` varchar(20) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  PRIMARY KEY (`zip_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2 个解决方案

#1

I found that I only needed two lines to fix my issue, I added one 0 at the top and 1 at the bottom and I was good. Sorry to waste your time...

我发现我只需要两行来修复我的问题,我在顶部添加了一个0,在底部添加了一个,我很好。抱歉浪费你的时间......

SET FOREIGN_KEY_CHECKS = 0;

SET FOREIGN_KEY_CHECKS = 1;

#2

the easiest way would be to do it via commandline like this:

最简单的方法是通过命令行这样做:

mysql db_name < backup-file.sql

this executes your sql file in one transaction. If you execute your stuff in one transaction then you won't get the foreign key errors.

这会在一个事务中执行您的sql文件。如果您在一个事务中执行您的东西,那么您将不会得到外键错误。

#1