I have searched questions about this problem:
but I find they are not similar to my case.
Here is my tables:
Table 1 history:
create table if not exists history(
worker_num int(11),
cust_num int(11),
primary key (cust_num,worker_num)
)engine=innodb, default charset=utf8;
Table 2 customer:
drop table if exists customer;
create table if not exists customer(
cust_number int(11) not null,
foreign key (cust_number) references history(cust_num)
)engine=innodb, default charset utf8;
Table 3 worker:
drop table if exists worker;
create table if not exists worker(
worker_number int(11) not null,
foreign key (worker_number) references history(worker_num)
)engine=innodb, default charset=utf8;
I can create Table 1 and Table 2 successfully. However, when I try to create Table 3, It throws me an error like below:
Failed to add the foreign key constraint. Missing index for constraint 'fk_customer' in the referenced table 'history
Error code 1822.'
Question 01:
I know this is the problem of index. Because I found that if I put the Table3 creating code before Table2, I cannot create Table 2 successfully. Thus, I know it must be caused by when the Table3 calls
foreign key (worker_number) references history(worker_num)
it will try to find the first primary key, primary key (cust_num,worker_num), in Table1 as its corresponding primary. However, the first primary key in table 1 is cust_num and this cust_num is not the correct corresponding primary key to foreign key worker_num. Thus, it throws an error.
However, I search official Mysql document and find the description of index. It says:
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
Here, you can see that Such an index is created on the referencing table automatically.
So, I'm very curious about if the referencing table is created automatically, why we cannot create child tables in any orders?(in my case, why should I create table 2 first or I will fail creating tables?)
Question 2:
In my case, I want define a foreign key in each child tables(table 2 and table 3) references to the primary keys in parent table(table 1). How to solve this index problem?
Appreciate any helps!!! Thanks!
