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`))
Improve this question
1
  • 3
    I’m voting to close this question because the answer was posted in the comments by the Question's owner. No further action needs to be taken
    – RolandoMySQLDBA
    Apr 23, 2020 at 18:15

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 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, 2016 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, 2016 at 13:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.