I am able to create table sales_order in database archived_db with the same schema as sales_order table from main database prod_db using below query:
CREATE TABLE archived_db.sales_order LIKE prod_db.sales_order
It is working fine and creating sales_order table in archived_db with same table structure as in prod_db. One thing that is not copied/created is the foreign key.
so, my question is, is it possible to create table B using schema of table A with the same foreign_keys?
You can use this:
SHOW CREATE TABLE prod_db.sales_order
This shows the table definition in the syntax of a CREATE TABLE statement.
Capture the result of this into a string, and then run that string as an SQL statement.
I would suppose that CREATE TABLE <B> LIKE <A> doesn't support foreign keys because foreign keys are a feature that depends on the storage engine. The engine-independent metadata does not store foreign key definitions. This is one of the unexpected consequences of MySQL's strange history and architecture of pluggable storage engines.
Re your comment:
ERROR 1215 (HY000): Cannot add foreign key constraint
Does the parent table exist in the archived db schema in which you are creating the copy table? I imagine the foreign key definition does not qualify the table name, and assumes that the parent table is in the same schema as the sales_order table.
If you need to specify which schema the foreign key references, it's probably best to create the table using CREATE TABLE LIKE as you were before, and then add foreign key(s) using ALTER TABLE.