Search code examples
mysqlforeign-keys

how to get fields foreign key(table and field) php mysqli

How can i get foreign key field like in phpMyAdmin using php and mysqli?
see photo:

Solution

  • All the constraints are listed in INFORMATION_SCHEMA.TABLE_CONSTRAINTS:

    mysql> create table test.foo (id int primary key);
mysql> create table test.bar (fooid int, foreign key (fooid) references foo(id));

mysql> select * from information_schema.table_constraints 
    where constraint_schema = 'test'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: bar_ibfk_1
      TABLE_SCHEMA: test
        TABLE_NAME: bar
   CONSTRAINT_TYPE: FOREIGN KEY
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: PRIMARY
      TABLE_SCHEMA: test
        TABLE_NAME: foo
   CONSTRAINT_TYPE: PRIMARY KEY

    And all the columns in those constraints are in INFORMATION_SCHEMA.KEY_COLUMN_USAGE:

    mysql> select * from information_schema.key_column_usage 
    where constraint_schema = 'test'\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: bar_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: bar
                  COLUMN_NAME: fooid
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test
        REFERENCED_TABLE_NAME: foo
       REFERENCED_COLUMN_NAME: id
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: foo
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL