Check out the image below. In the
If we execute this schema creation:
create table parent ( pid int auto_increment primary key, theirName varchar(100) not null ); drop table if exists child; create table child ( cid int auto_increment primary key, theirName varchar(100) not null, pid int not null, foreign key `fk_c2p` (pid) references parent(pid) );
Examine what happened to the child:
mysql> show create table child \G; CREATE TABLE `child` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `theirName` varchar(100) NOT NULL, `pid` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_c2p` (`pid`), -- ******************** AUTO created by mysql CONSTRAINT `child_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
From the manual page Using FOREIGN KEY Constraints:
... index_name represents a foreign key ID. The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key. Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules:
If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used.
If neither a CONSTRAINT symbol or FOREIGN KEY index_name is defined, the foreign key index name is generated using the name of the referencing foreign key column.
So, back to your questions.
A. Why are they created? They are created because mysql creates them as specified above. They facilitate speedy reversal lookups. When a parent row is to be deleted, a fast non-table scan of children is mandated to allow or disallow the parent row removal. The auto-generated key (or one already satisfying it) is used for this purpose.
B. Should you delete them? No. Why not? Read A.
C. How do you "auto-insert the key values from the deeper tables": you acquire the id of the parent (anywhere in the hierarchy) ahead of time such as using
LAST_INSERT_ID() or other program logic.