load data infile in mysql server 5.5 can't load data when foreign key exist Announcing the...
Is it true that "carbohydrates are of no use for the basal metabolic need"?
Why does Python start at index 1 when iterating an array backwards?
Is 1 ppb equal to 1 μg/kg?
Are my PIs rude or am I just being too sensitive?
Why was the term "discrete" used in discrete logarithm?
What is the longest distance a 13th-level monk can jump while attacking on the same turn?
What is the correct way to use the pinch test for dehydration?
"Seemed to had" is it correct?
Why are there no cargo aircraft with "flying wing" design?
Single word antonym of "flightless"
Java 8 stream max() function argument type Comparator vs Comparable
Is there a "higher Segal conjecture"?
Can a non-EU citizen traveling with me come with me through the EU passport line?
What is the musical term for a note that continously plays through a melody?
Can inflation occur in a positive-sum game currency system such as the Stack Exchange reputation system?
3 doors, three guards, one stone
Right-skewed distribution with mean equals to mode?
If a contract sometimes uses the wrong name, is it still valid?
When to stop saving and start investing?
When -s is used with third person singular. What's its use in this context?
IndentationError when pasting code in Python 3 interpreter mode
Letter Boxed validator
Does surprise arrest existing movement?
Check which numbers satisfy the condition [A*B*C = A! + B! + C!]
load data infile in mysql server 5.5 can't load data when foreign key exist
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)MySQL, foreign key, can't create table error 150Foreign key in MySQLESCAPED BY clause in LOAD DATA INFILEEnabling LOAD DATA LOCAL INFILE in mysqlMySQL foreign key errorMySQL - Foreign Key Problemcan't create foreign key in sqlitePostgres Can't Add Foreign Key ConstraintCan't add Foreign Key ConstraintCan't define a foreign key
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have a database (TPC-DS benchmark base) with 24 tables(17 dimensions tables, 7 facts tables)
I generated this database then I created it in mysql server 5.5
I used the instruction load data local infile it works only when the table have not a foreign key, but tables with foreign key don't work and I can't find a solution to this error
here an example of a table and the error:
This is table without foreign key:
create table date_dim
(
d_date_sk integer not null,
d_date_id char(16) not null,
d_date date ,
d_month_seq integer ,
d_week_seq integer ,
d_quarter_seq integer ,
d_year integer ,
d_dow integer ,
d_moy integer ,
d_dom integer ,
d_qoy integer ,
d_fy_year integer ,
d_fy_quarter_seq integer ,
d_fy_week_seq integer ,
d_day_name char(9) ,
d_quarter_name char(6) ,
d_holiday char(1) ,
d_weekend char(1) ,
d_following_holiday char(1) ,
d_first_dom integer ,
d_last_dom integer ,
d_same_day_ly integer ,
d_same_day_lq integer ,
d_current_day char(1) ,
d_current_week char(1) ,
d_current_month char(1) ,
d_current_quarter char(1) ,
d_current_year char(1) ,
primary key (d_date_sk)
);
This is the table with foreign key:
create table web_site
(
web_site_sk integer not null,
web_site_id char(16) not null,
web_rec_start_date date ,
web_rec_end_date date ,
web_name varchar(50) ,
web_open_date_sk integer ,
web_close_date_sk integer ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id integer ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id integer ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2) ,
primary key (web_site_sk)
CONSTRAINT web_d1 FOREIGN KEY (web_close_date_sk) REFERENCES date_dim (d_date_sk)
CONSTRAINT web_d2 FOREIGN KEY (web_open_date_sk) REFERENCES date_dim (d_date_sk)
);
Here are some of the data from the table date_dim:
2415022|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
2415023|AAAAAAAAPKJNECAA|1900-01-03|0|1|1|1900|2|1|3|1|1900|1|1|Tuesday|1900Q1|N|N|N|2415021|2415020|2414658|2414931|N|N|N|N|N|
2415024|AAAAAAAAALJNECAA|1900-01-04|0|1|1|1900|3|1|4|1|1900|1|1|Wednesday|1900Q1|N|N|N|2415021|2415020|2414659|2414932|N|N|N|N|N|
2415025|AAAAAAAABLJNECAA|1900-01-05|0|1|1|1900|4|1|5|1|1900|1|1|Thursday|1900Q1|N|N|N|2415021|2415020|2414660|2414933|N|N|N|N|N|
2415026|AAAAAAAACLJNECAA|1900-01-06|0|1|1|1900|5|1|6|1|1900|1|1|Friday|1900Q1|N|Y|N|2415021|2415020|2414661|2414934|N|N|N|N|N|
2415027|AAAAAAAADLJNECAA|1900-01-07|0|1|1|1900|6|1|7|1|1900|1|1|Saturday|1900Q1|N|Y|N|2415021|2415020|2414662|2414935|N|N|N|N|N|
2415028|AAAAAAAAELJNECAA|1900-01-08|0|1|1|1900|0|1|8|1|1900|1|1|Sunday|1900Q1|N|N|N|2415021|2415020|2414663|2414936|N|N|N|N|N|
2415029|AAAAAAAAFLJNECAA|1900-01-09|0|2|1|1900|1|1|9|1|1900|1|2|Monday|1900Q1|N|N|N|2415021|2415020|2414664|2414937|N|N|N|N|N|
2415030|AAAAAAAAGLJNECAA|1900-01-10|0|2|1|1900|2|1|10|1|1900|1|2|Tuesday|1900Q1|N|N|N|2415021|2415020|2414665|2414938|N|N|N|N|N|
2415031|AAAAAAAAHLJNECAA|1900-01-11|0|2|1|1900|3|1|11|1|1900|1|2|Wednesday|1900Q1|N|N|N|2415021|2415020|2414666|2414939|N|N|N|N|N|
2415032|AAAAAAAAILJNECAA|1900-01-12|0|2|1|1900|4|1|12|1|1900|1|2|Thursday|1900Q1|N|N|N|2415021|2415020|2414667|2414940|N|N|N|N|N|
2415033|AAAAAAAAJLJNECAA|1900-01-13|0|2|1|1900|5|1|13|1|1900|1|2|Friday|1900Q1|N|Y|N|2415021|2415020|2414668|2414941|N|N|N|N|N|
Data from table web_site
1|AAAAAAAABAAAAAAA|1997-08-16||site_0|2450807||Unknown|Ronald Shaffer|4|Grey lines ought to result indeed centres. Tod|Well similar decisions used to keep hardly democratic, personal priorities.|Joe George|6|cally|51|Dogwood Sunset|Ln|Suite 330|Midway|Williamson County|TN|31904|United States|-5|0.10|
2|AAAAAAAACAAAAAAA|1997-08-16|2000-08-15|site_0|2450798|2447148|Unknown|Tommy Jones|6|Completely excellent things ought to pro|Lucky passengers know. Red details will not hang alive, international s|David Myers|4|ese|358|Ridge Wilson|Cir.|Suite 150|Midway|Williamson County|TN|31904|United States|-5|0.00|
3|AAAAAAAACAAAAAAA|2000-08-16||site_0|2450798|2447148|Unknown|Tommy Jones|3|Completely excellent things ought to pro|Particular, common seasons shall not indicate fully more single decisions; |David Myers|4|ese|753|7th |Pkwy|Suite 210|Midway|Williamson County|TN|31904|United States|-5|0.02|
4|AAAAAAAAEAAAAAAA|1997-08-16|1999-08-16|site_0|2450781|2448956|Unknown|Harold Wilson|5|As strong notes deal questions. Proud visito|Deeply small books cannot extend all similar, clear historians. Free, new camp|James Harris|5|anti|805|Hill Hill|Dr.|Suite R|Midway|Williamson County|TN|31904|United States|-5|0.08|
5|AAAAAAAAEAAAAAAA|1999-08-17|2001-08-15|site_0|2450781|2448956|Unknown|Harold Wilson|5|Wide, final representat|Deeply small books cannot extend all similar, clear historians. Free, new camp|Edward George|1|ought|805|2nd 3rd|Road|Suite R|Fairview|Williamson County|TN|31904|United States|-5|0.08|
This is the error:
mysql> LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n';
Error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tpcds`.`web_site`, CONSTRAINT `web_d1` FOREIGN KEY (`web_close_date_sk`) REFERENCES `date_dim` (`d_date_sk`))
mysql-5.5 foreign-key
edited Jan 1 '16 at 12:20
Mat
8,20223338
asked Jan 1 '16 at 11:12
sirinasirina
1
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a database (TPC-DS benchmark base) with 24 tables(17 dimensions tables, 7 facts tables)
I generated this database then I created it in mysql server 5.5
I used the instruction load data local infile it works only when the table have not a foreign key, but tables with foreign key don't work and I can't find a solution to this error
here an example of a table and the error:
This is table without foreign key:
create table date_dim
(
d_date_sk integer not null,
d_date_id char(16) not null,
d_date date ,
d_month_seq integer ,
d_week_seq integer ,
d_quarter_seq integer ,
d_year integer ,
d_dow integer ,
d_moy integer ,
d_dom integer ,
d_qoy integer ,
d_fy_year integer ,
d_fy_quarter_seq integer ,
d_fy_week_seq integer ,
d_day_name char(9) ,
d_quarter_name char(6) ,
d_holiday char(1) ,
d_weekend char(1) ,
d_following_holiday char(1) ,
d_first_dom integer ,
d_last_dom integer ,
d_same_day_ly integer ,
d_same_day_lq integer ,
d_current_day char(1) ,
d_current_week char(1) ,
d_current_month char(1) ,
d_current_quarter char(1) ,
d_current_year char(1) ,
primary key (d_date_sk)
);
This is the table with foreign key:
create table web_site
(
web_site_sk integer not null,
web_site_id char(16) not null,
web_rec_start_date date ,
web_rec_end_date date ,
web_name varchar(50) ,
web_open_date_sk integer ,
web_close_date_sk integer ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id integer ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id integer ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2) ,
primary key (web_site_sk)
CONSTRAINT web_d1 FOREIGN KEY (web_close_date_sk) REFERENCES date_dim (d_date_sk)
CONSTRAINT web_d2 FOREIGN KEY (web_open_date_sk) REFERENCES date_dim (d_date_sk)
);
Here are some of the data from the table date_dim:
2415022|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
2415023|AAAAAAAAPKJNECAA|1900-01-03|0|1|1|1900|2|1|3|1|1900|1|1|Tuesday|1900Q1|N|N|N|2415021|2415020|2414658|2414931|N|N|N|N|N|
2415024|AAAAAAAAALJNECAA|1900-01-04|0|1|1|1900|3|1|4|1|1900|1|1|Wednesday|1900Q1|N|N|N|2415021|2415020|2414659|2414932|N|N|N|N|N|
2415025|AAAAAAAABLJNECAA|1900-01-05|0|1|1|1900|4|1|5|1|1900|1|1|Thursday|1900Q1|N|N|N|2415021|2415020|2414660|2414933|N|N|N|N|N|
2415026|AAAAAAAACLJNECAA|1900-01-06|0|1|1|1900|5|1|6|1|1900|1|1|Friday|1900Q1|N|Y|N|2415021|2415020|2414661|2414934|N|N|N|N|N|
2415027|AAAAAAAADLJNECAA|1900-01-07|0|1|1|1900|6|1|7|1|1900|1|1|Saturday|1900Q1|N|Y|N|2415021|2415020|2414662|2414935|N|N|N|N|N|
2415028|AAAAAAAAELJNECAA|1900-01-08|0|1|1|1900|0|1|8|1|1900|1|1|Sunday|1900Q1|N|N|N|2415021|2415020|2414663|2414936|N|N|N|N|N|
2415029|AAAAAAAAFLJNECAA|1900-01-09|0|2|1|1900|1|1|9|1|1900|1|2|Monday|1900Q1|N|N|N|2415021|2415020|2414664|2414937|N|N|N|N|N|
2415030|AAAAAAAAGLJNECAA|1900-01-10|0|2|1|1900|2|1|10|1|1900|1|2|Tuesday|1900Q1|N|N|N|2415021|2415020|2414665|2414938|N|N|N|N|N|
2415031|AAAAAAAAHLJNECAA|1900-01-11|0|2|1|1900|3|1|11|1|1900|1|2|Wednesday|1900Q1|N|N|N|2415021|2415020|2414666|2414939|N|N|N|N|N|
2415032|AAAAAAAAILJNECAA|1900-01-12|0|2|1|1900|4|1|12|1|1900|1|2|Thursday|1900Q1|N|N|N|2415021|2415020|2414667|2414940|N|N|N|N|N|
2415033|AAAAAAAAJLJNECAA|1900-01-13|0|2|1|1900|5|1|13|1|1900|1|2|Friday|1900Q1|N|Y|N|2415021|2415020|2414668|2414941|N|N|N|N|N|
Data from table web_site
1|AAAAAAAABAAAAAAA|1997-08-16||site_0|2450807||Unknown|Ronald Shaffer|4|Grey lines ought to result indeed centres. Tod|Well similar decisions used to keep hardly democratic, personal priorities.|Joe George|6|cally|51|Dogwood Sunset|Ln|Suite 330|Midway|Williamson County|TN|31904|United States|-5|0.10|
2|AAAAAAAACAAAAAAA|1997-08-16|2000-08-15|site_0|2450798|2447148|Unknown|Tommy Jones|6|Completely excellent things ought to pro|Lucky passengers know. Red details will not hang alive, international s|David Myers|4|ese|358|Ridge Wilson|Cir.|Suite 150|Midway|Williamson County|TN|31904|United States|-5|0.00|
3|AAAAAAAACAAAAAAA|2000-08-16||site_0|2450798|2447148|Unknown|Tommy Jones|3|Completely excellent things ought to pro|Particular, common seasons shall not indicate fully more single decisions; |David Myers|4|ese|753|7th |Pkwy|Suite 210|Midway|Williamson County|TN|31904|United States|-5|0.02|
4|AAAAAAAAEAAAAAAA|1997-08-16|1999-08-16|site_0|2450781|2448956|Unknown|Harold Wilson|5|As strong notes deal questions. Proud visito|Deeply small books cannot extend all similar, clear historians. Free, new camp|James Harris|5|anti|805|Hill Hill|Dr.|Suite R|Midway|Williamson County|TN|31904|United States|-5|0.08|
5|AAAAAAAAEAAAAAAA|1999-08-17|2001-08-15|site_0|2450781|2448956|Unknown|Harold Wilson|5|Wide, final representat|Deeply small books cannot extend all similar, clear historians. Free, new camp|Edward George|1|ought|805|2nd 3rd|Road|Suite R|Fairview|Williamson County|TN|31904|United States|-5|0.08|
This is the error:
mysql> LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n';
Error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tpcds`.`web_site`, CONSTRAINT `web_d1` FOREIGN KEY (`web_close_date_sk`) REFERENCES `date_dim` (`d_date_sk`))
mysql-5.5 foreign-key
edited Jan 1 '16 at 12:20
Mat
8,20223338
asked Jan 1 '16 at 11:12
sirinasirina
1
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a database (TPC-DS benchmark base) with 24 tables(17 dimensions tables, 7 facts tables)
I generated this database then I created it in mysql server 5.5
I used the instruction load data local infile it works only when the table have not a foreign key, but tables with foreign key don't work and I can't find a solution to this error
here an example of a table and the error:
This is table without foreign key:
create table date_dim
(
d_date_sk integer not null,
d_date_id char(16) not null,
d_date date ,
d_month_seq integer ,
d_week_seq integer ,
d_quarter_seq integer ,
d_year integer ,
d_dow integer ,
d_moy integer ,
d_dom integer ,
d_qoy integer ,
d_fy_year integer ,
d_fy_quarter_seq integer ,
d_fy_week_seq integer ,
d_day_name char(9) ,
d_quarter_name char(6) ,
d_holiday char(1) ,
d_weekend char(1) ,
d_following_holiday char(1) ,
d_first_dom integer ,
d_last_dom integer ,
d_same_day_ly integer ,
d_same_day_lq integer ,
d_current_day char(1) ,
d_current_week char(1) ,
d_current_month char(1) ,
d_current_quarter char(1) ,
d_current_year char(1) ,
primary key (d_date_sk)
);
This is the table with foreign key:
create table web_site
(
web_site_sk integer not null,
web_site_id char(16) not null,
web_rec_start_date date ,
web_rec_end_date date ,
web_name varchar(50) ,
web_open_date_sk integer ,
web_close_date_sk integer ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id integer ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id integer ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2) ,
primary key (web_site_sk)
CONSTRAINT web_d1 FOREIGN KEY (web_close_date_sk) REFERENCES date_dim (d_date_sk)
CONSTRAINT web_d2 FOREIGN KEY (web_open_date_sk) REFERENCES date_dim (d_date_sk)
);
Here are some of the data from the table date_dim:
2415022|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
2415023|AAAAAAAAPKJNECAA|1900-01-03|0|1|1|1900|2|1|3|1|1900|1|1|Tuesday|1900Q1|N|N|N|2415021|2415020|2414658|2414931|N|N|N|N|N|
2415024|AAAAAAAAALJNECAA|1900-01-04|0|1|1|1900|3|1|4|1|1900|1|1|Wednesday|1900Q1|N|N|N|2415021|2415020|2414659|2414932|N|N|N|N|N|
2415025|AAAAAAAABLJNECAA|1900-01-05|0|1|1|1900|4|1|5|1|1900|1|1|Thursday|1900Q1|N|N|N|2415021|2415020|2414660|2414933|N|N|N|N|N|
2415026|AAAAAAAACLJNECAA|1900-01-06|0|1|1|1900|5|1|6|1|1900|1|1|Friday|1900Q1|N|Y|N|2415021|2415020|2414661|2414934|N|N|N|N|N|
2415027|AAAAAAAADLJNECAA|1900-01-07|0|1|1|1900|6|1|7|1|1900|1|1|Saturday|1900Q1|N|Y|N|2415021|2415020|2414662|2414935|N|N|N|N|N|
2415028|AAAAAAAAELJNECAA|1900-01-08|0|1|1|1900|0|1|8|1|1900|1|1|Sunday|1900Q1|N|N|N|2415021|2415020|2414663|2414936|N|N|N|N|N|
2415029|AAAAAAAAFLJNECAA|1900-01-09|0|2|1|1900|1|1|9|1|1900|1|2|Monday|1900Q1|N|N|N|2415021|2415020|2414664|2414937|N|N|N|N|N|
2415030|AAAAAAAAGLJNECAA|1900-01-10|0|2|1|1900|2|1|10|1|1900|1|2|Tuesday|1900Q1|N|N|N|2415021|2415020|2414665|2414938|N|N|N|N|N|
2415031|AAAAAAAAHLJNECAA|1900-01-11|0|2|1|1900|3|1|11|1|1900|1|2|Wednesday|1900Q1|N|N|N|2415021|2415020|2414666|2414939|N|N|N|N|N|
2415032|AAAAAAAAILJNECAA|1900-01-12|0|2|1|1900|4|1|12|1|1900|1|2|Thursday|1900Q1|N|N|N|2415021|2415020|2414667|2414940|N|N|N|N|N|
2415033|AAAAAAAAJLJNECAA|1900-01-13|0|2|1|1900|5|1|13|1|1900|1|2|Friday|1900Q1|N|Y|N|2415021|2415020|2414668|2414941|N|N|N|N|N|
Data from table web_site
1|AAAAAAAABAAAAAAA|1997-08-16||site_0|2450807||Unknown|Ronald Shaffer|4|Grey lines ought to result indeed centres. Tod|Well similar decisions used to keep hardly democratic, personal priorities.|Joe George|6|cally|51|Dogwood Sunset|Ln|Suite 330|Midway|Williamson County|TN|31904|United States|-5|0.10|
2|AAAAAAAACAAAAAAA|1997-08-16|2000-08-15|site_0|2450798|2447148|Unknown|Tommy Jones|6|Completely excellent things ought to pro|Lucky passengers know. Red details will not hang alive, international s|David Myers|4|ese|358|Ridge Wilson|Cir.|Suite 150|Midway|Williamson County|TN|31904|United States|-5|0.00|
3|AAAAAAAACAAAAAAA|2000-08-16||site_0|2450798|2447148|Unknown|Tommy Jones|3|Completely excellent things ought to pro|Particular, common seasons shall not indicate fully more single decisions; |David Myers|4|ese|753|7th |Pkwy|Suite 210|Midway|Williamson County|TN|31904|United States|-5|0.02|
4|AAAAAAAAEAAAAAAA|1997-08-16|1999-08-16|site_0|2450781|2448956|Unknown|Harold Wilson|5|As strong notes deal questions. Proud visito|Deeply small books cannot extend all similar, clear historians. Free, new camp|James Harris|5|anti|805|Hill Hill|Dr.|Suite R|Midway|Williamson County|TN|31904|United States|-5|0.08|
5|AAAAAAAAEAAAAAAA|1999-08-17|2001-08-15|site_0|2450781|2448956|Unknown|Harold Wilson|5|Wide, final representat|Deeply small books cannot extend all similar, clear historians. Free, new camp|Edward George|1|ought|805|2nd 3rd|Road|Suite R|Fairview|Williamson County|TN|31904|United States|-5|0.08|
This is the error:
mysql> LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n';
Error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tpcds`.`web_site`, CONSTRAINT `web_d1` FOREIGN KEY (`web_close_date_sk`) REFERENCES `date_dim` (`d_date_sk`))
mysql-5.5 foreign-key
edited Jan 1 '16 at 12:20
Mat
8,20223338
asked Jan 1 '16 at 11:12
sirinasirina
1
I have a database (TPC-DS benchmark base) with 24 tables(17 dimensions tables, 7 facts tables)
I generated this database then I created it in mysql server 5.5
I used the instruction load data local infile it works only when the table have not a foreign key, but tables with foreign key don't work and I can't find a solution to this error
here an example of a table and the error:
This is table without foreign key:
create table date_dim
(
d_date_sk integer not null,
d_date_id char(16) not null,
d_date date ,
d_month_seq integer ,
d_week_seq integer ,
d_quarter_seq integer ,
d_year integer ,
d_dow integer ,
d_moy integer ,
d_dom integer ,
d_qoy integer ,
d_fy_year integer ,
d_fy_quarter_seq integer ,
d_fy_week_seq integer ,
d_day_name char(9) ,
d_quarter_name char(6) ,
d_holiday char(1) ,
d_weekend char(1) ,
d_following_holiday char(1) ,
d_first_dom integer ,
d_last_dom integer ,
d_same_day_ly integer ,
d_same_day_lq integer ,
d_current_day char(1) ,
d_current_week char(1) ,
d_current_month char(1) ,
d_current_quarter char(1) ,
d_current_year char(1) ,
primary key (d_date_sk)
);
This is the table with foreign key:
create table web_site
(
web_site_sk integer not null,
web_site_id char(16) not null,
web_rec_start_date date ,
web_rec_end_date date ,
web_name varchar(50) ,
web_open_date_sk integer ,
web_close_date_sk integer ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id integer ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id integer ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2) ,
primary key (web_site_sk)
CONSTRAINT web_d1 FOREIGN KEY (web_close_date_sk) REFERENCES date_dim (d_date_sk)
CONSTRAINT web_d2 FOREIGN KEY (web_open_date_sk) REFERENCES date_dim (d_date_sk)
);
Here are some of the data from the table date_dim:
2415022|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
2415023|AAAAAAAAPKJNECAA|1900-01-03|0|1|1|1900|2|1|3|1|1900|1|1|Tuesday|1900Q1|N|N|N|2415021|2415020|2414658|2414931|N|N|N|N|N|
2415024|AAAAAAAAALJNECAA|1900-01-04|0|1|1|1900|3|1|4|1|1900|1|1|Wednesday|1900Q1|N|N|N|2415021|2415020|2414659|2414932|N|N|N|N|N|
2415025|AAAAAAAABLJNECAA|1900-01-05|0|1|1|1900|4|1|5|1|1900|1|1|Thursday|1900Q1|N|N|N|2415021|2415020|2414660|2414933|N|N|N|N|N|
2415026|AAAAAAAACLJNECAA|1900-01-06|0|1|1|1900|5|1|6|1|1900|1|1|Friday|1900Q1|N|Y|N|2415021|2415020|2414661|2414934|N|N|N|N|N|
2415027|AAAAAAAADLJNECAA|1900-01-07|0|1|1|1900|6|1|7|1|1900|1|1|Saturday|1900Q1|N|Y|N|2415021|2415020|2414662|2414935|N|N|N|N|N|
2415028|AAAAAAAAELJNECAA|1900-01-08|0|1|1|1900|0|1|8|1|1900|1|1|Sunday|1900Q1|N|N|N|2415021|2415020|2414663|2414936|N|N|N|N|N|
2415029|AAAAAAAAFLJNECAA|1900-01-09|0|2|1|1900|1|1|9|1|1900|1|2|Monday|1900Q1|N|N|N|2415021|2415020|2414664|2414937|N|N|N|N|N|
2415030|AAAAAAAAGLJNECAA|1900-01-10|0|2|1|1900|2|1|10|1|1900|1|2|Tuesday|1900Q1|N|N|N|2415021|2415020|2414665|2414938|N|N|N|N|N|
2415031|AAAAAAAAHLJNECAA|1900-01-11|0|2|1|1900|3|1|11|1|1900|1|2|Wednesday|1900Q1|N|N|N|2415021|2415020|2414666|2414939|N|N|N|N|N|
2415032|AAAAAAAAILJNECAA|1900-01-12|0|2|1|1900|4|1|12|1|1900|1|2|Thursday|1900Q1|N|N|N|2415021|2415020|2414667|2414940|N|N|N|N|N|
2415033|AAAAAAAAJLJNECAA|1900-01-13|0|2|1|1900|5|1|13|1|1900|1|2|Friday|1900Q1|N|Y|N|2415021|2415020|2414668|2414941|N|N|N|N|N|
Data from table web_site
1|AAAAAAAABAAAAAAA|1997-08-16||site_0|2450807||Unknown|Ronald Shaffer|4|Grey lines ought to result indeed centres. Tod|Well similar decisions used to keep hardly democratic, personal priorities.|Joe George|6|cally|51|Dogwood Sunset|Ln|Suite 330|Midway|Williamson County|TN|31904|United States|-5|0.10|
2|AAAAAAAACAAAAAAA|1997-08-16|2000-08-15|site_0|2450798|2447148|Unknown|Tommy Jones|6|Completely excellent things ought to pro|Lucky passengers know. Red details will not hang alive, international s|David Myers|4|ese|358|Ridge Wilson|Cir.|Suite 150|Midway|Williamson County|TN|31904|United States|-5|0.00|
3|AAAAAAAACAAAAAAA|2000-08-16||site_0|2450798|2447148|Unknown|Tommy Jones|3|Completely excellent things ought to pro|Particular, common seasons shall not indicate fully more single decisions; |David Myers|4|ese|753|7th |Pkwy|Suite 210|Midway|Williamson County|TN|31904|United States|-5|0.02|
4|AAAAAAAAEAAAAAAA|1997-08-16|1999-08-16|site_0|2450781|2448956|Unknown|Harold Wilson|5|As strong notes deal questions. Proud visito|Deeply small books cannot extend all similar, clear historians. Free, new camp|James Harris|5|anti|805|Hill Hill|Dr.|Suite R|Midway|Williamson County|TN|31904|United States|-5|0.08|
5|AAAAAAAAEAAAAAAA|1999-08-17|2001-08-15|site_0|2450781|2448956|Unknown|Harold Wilson|5|Wide, final representat|Deeply small books cannot extend all similar, clear historians. Free, new camp|Edward George|1|ought|805|2nd 3rd|Road|Suite R|Fairview|Williamson County|TN|31904|United States|-5|0.08|
This is the error:
mysql> LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n';
Error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tpcds`.`web_site`, CONSTRAINT `web_d1` FOREIGN KEY (`web_close_date_sk`) REFERENCES `date_dim` (`d_date_sk`))
mysql-5.5 foreign-key
mysql-5.5 foreign-key
edited Jan 1 '16 at 12:20
Mat
8,20223338
asked Jan 1 '16 at 11:12
sirinasirina
1
edited Jan 1 '16 at 12:20
Mat
8,20223338
asked Jan 1 '16 at 11:12
sirinasirina
1
edited Jan 1 '16 at 12:20
Mat
8,20223338
edited Jan 1 '16 at 12:20
Mat
8,20223338
edited Jan 1 '16 at 12:20
Mat
8,20223338
8,20223338
asked Jan 1 '16 at 11:12
sirinasirina
1
asked Jan 1 '16 at 11:12
sirinasirina
1
asked Jan 1 '16 at 11:12
sirinasirina
1
1
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This error usually occurs, when you are trying to insert or update a data instance to a foreign key data field in child table, which is not exist in the parent table.
You can solve this issue by inserting the required values to the parent table before trying to insert it into the child table.
Specifically, you must insert values to the parent table data field d_date_sk before loading values to child table data field web_close_date_sk. Or you must load the values to web_close_date_sk, which is already exists in d_date_sk. I hope this will solve the issue.
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f124978%2fload-data-infile-in-mysql-server-5-5-cant-load-data-when-foreign-key-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This error usually occurs, when you are trying to insert or update a data instance to a foreign key data field in child table, which is not exist in the parent table.
You can solve this issue by inserting the required values to the parent table before trying to insert it into the child table.
Specifically, you must insert values to the parent table data field d_date_sk before loading values to child table data field web_close_date_sk. Or you must load the values to web_close_date_sk, which is already exists in d_date_sk. I hope this will solve the issue.
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
add a comment |
This error usually occurs, when you are trying to insert or update a data instance to a foreign key data field in child table, which is not exist in the parent table.
You can solve this issue by inserting the required values to the parent table before trying to insert it into the child table.
Specifically, you must insert values to the parent table data field d_date_sk before loading values to child table data field web_close_date_sk. Or you must load the values to web_close_date_sk, which is already exists in d_date_sk. I hope this will solve the issue.
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
add a comment |
This error usually occurs, when you are trying to insert or update a data instance to a foreign key data field in child table, which is not exist in the parent table.
You can solve this issue by inserting the required values to the parent table before trying to insert it into the child table.
Specifically, you must insert values to the parent table data field d_date_sk before loading values to child table data field web_close_date_sk. Or you must load the values to web_close_date_sk, which is already exists in d_date_sk. I hope this will solve the issue.
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
This error usually occurs, when you are trying to insert or update a data instance to a foreign key data field in child table, which is not exist in the parent table.
You can solve this issue by inserting the required values to the parent table before trying to insert it into the child table.
Specifically, you must insert values to the parent table data field d_date_sk before loading values to child table data field web_close_date_sk. Or you must load the values to web_close_date_sk, which is already exists in d_date_sk. I hope this will solve the issue.
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
answered Jan 1 '16 at 12:20
rathishDBArathishDBA
1,10431227
1,10431227
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
add a comment |
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
As well as doing what Rathish says, you could remove the FK constraints from your table definition, load all of your data and then add your FKs (example here).
– Vérace
Jan 3 '16 at 17:09
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
Thanks for all, this problem is resolved: first I disabled the constraint of foreign key, then load the data and finally enable the foreign key like this: set foreign_key_checks=0; LOAD DATA LOCAL INFILE '/home/user/Bureau/TPC-DS/tools/web_site.dat' INTO TABLE web_site FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n'; set foreign_key_checks=1;
– sirina
Jan 4 '16 at 13:42
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f124978%2fload-data-infile-in-mysql-server-5-5-cant-load-data-when-foreign-key-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
