Is it possible to create one index for both primary and foreign key?

Let’s suppose we have two tables A and B and between them one-to-one relation.

CREATE TABLE IF NOT EXISTS A 
(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS B 
(
    ID INT UNSIGNED NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (ID) REFERENCES A(ID)
        ON UPDATE CASCADE ON DELETE CASCADE
);

B.ID key will be used as foreign key in tables about which A doesn’t know. When row is deleted from A there also will be deletion from other tables that are linked to B. As we see in B one column is at the same time primary and foreign key. As I know keys use indexes. So, is it possible to make these two keys use the same index? Does it depend on RDBMS? Or there is something wrong in my understanding?

Answer

As I know [foreign] keys use indexes

This is false. I am guessing that your experience with databases is limited to MySQL/MariaDB. These are two databases where a foreign key definition does created an index on the referencing table.

In most databases, a foreign key definition does NOT create an index on the referencing table. Another difference is that most databases (and I’m pretty sure the standard as well) requires that the referenced key be either a primary key or unique key. That doesn’t affect you in this case, but it is another deviation from the standard in MySQL in this area.