cakephp / phinx

     * @param string $tableName Table Name

     * @return array

     */

    protected function getForeignKeys($tableName)

    {

        $foreignKeys = [];

        $rows = $this->fetchAll(sprintf(

            "SELECT

                    tc.constraint_name,

                    tc.table_name, kcu.column_name,

                    ccu.table_name AS referenced_table_name,

                    ccu.column_name AS referenced_column_name

                FROM

                    information_schema.table_constraints AS tc

                    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name

                    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name

                WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = '%s'

                ORDER BY kcu.position_in_unique_constraint",

            $tableName

        ));

        foreach ($rows as $row) {

            $foreignKeys[$row['constraint_name']]['table'] = $row['table_name'];

            $foreignKeys[$row['constraint_name']]['columns'][] = $row['column_name'];

            $foreignKeys[$row['constraint_name']]['referenced_table'] = $row['referenced_table_name'];

            $foreignKeys[$row['constraint_name']]['referenced_columns'][] = $row['referenced_column_name'];

        }

        return $foreignKeys;

    }

    /**

     * @param string $tableName Table Name

     * @return array

     */

    protected function getForeignKeys($tableName)

    {

        $foreignKeys = [];

        $rows = $this->fetchAll(sprintf(

            "SELECT

                    tc.constraint_name,

                    tc.table_name, kcu.column_name,

                    ccu.table_name AS referenced_table_name,

                    ccu.column_name AS referenced_column_name

                FROM

                    information_schema.table_constraints AS tc

                    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name

                    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name

                WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = '%s'

                ORDER BY kcu.ordinal_position",

            $tableName

        ));

        foreach ($rows as $row) {

            $foreignKeys[$row['constraint_name']]['table'] = $row['table_name'];

            $foreignKeys[$row['constraint_name']]['columns'][] = $row['column_name'];

            $foreignKeys[$row['constraint_name']]['referenced_table'] = $row['referenced_table_name'];

            $foreignKeys[$row['constraint_name']]['referenced_columns'][] = $row['referenced_column_name'];

        }

        return $foreignKeys;

    }

    /**

     * {@inheritdoc}

     * @param string $tableName Table Name

     * @return array

     */

    protected function getForeignKeys($tableName)

    {

        $foreignKeys = [];

        $rows = $this->fetchAll(sprintf(

            "SELECT

              CONSTRAINT_NAME,

              TABLE_NAME,

              COLUMN_NAME,

              REFERENCED_TABLE_NAME,

              REFERENCED_COLUMN_NAME

            FROM information_schema.KEY_COLUMN_USAGE

            WHERE REFERENCED_TABLE_SCHEMA = DATABASE()

              AND REFERENCED_TABLE_NAME IS NOT NULL

              AND TABLE_NAME = '%s'

            ORDER BY POSITION_IN_UNIQUE_CONSTRAINT",

            $tableName

        ));

        foreach ($rows as $row) {

            $foreignKeys[$row['CONSTRAINT_NAME']]['table'] = $row['TABLE_NAME'];

            $foreignKeys[$row['CONSTRAINT_NAME']]['columns'][] = $row['COLUMN_NAME'];

            $foreignKeys[$row['CONSTRAINT_NAME']]['referenced_table'] = $row['REFERENCED_TABLE_NAME'];

            $foreignKeys[$row['CONSTRAINT_NAME']]['referenced_columns'][] = $row['REFERENCED_COLUMN_NAME'];

        }

        return $foreignKeys;

    }

    /**

     * @param string $type Type of Constraint Type (R, P)

     * @return array

     */

    protected function getForeignKeys($tableName, $type = 'R')

    {

        $foreignKeys = [];

        $rows = $this->fetchAll(sprintf(

            "SELECT a.CONSTRAINT_NAME, a.TABLE_NAME, b.COLUMN_NAME, 

                    (SELECT c.TABLE_NAME from ALL_CONS_COLUMNS c 

                    WHERE c.CONSTRAINT_NAME = a.R_CONSTRAINT_NAME) referenced_table_name,

                    (SELECT c.COLUMN_NAME from ALL_CONS_COLUMNS c 

                    WHERE c.CONSTRAINT_NAME = a.R_CONSTRAINT_NAME) referenced_column_name

                    FROM all_constraints a JOIN ALL_CONS_COLUMNS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME

                    WHERE a.table_name = '%s'

                    AND CONSTRAINT_TYPE = '%s'",

            $tableName,

            $type

        ));

        foreach ($rows as $row) {

            $foreignKeys[$row['CONSTRAINT_NAME']]['TABLE'] = $row['TABLE_NAME'];

            $foreignKeys[$row['CONSTRAINT_NAME']]['COLUMNS'][] = $row['COLUMN_NAME'];

            $foreignKeys[$row['CONSTRAINT_NAME']]['REFERENCED_TABLE'] = $row['REFERENCED_TABLE_NAME'];

            $foreignKeys[$row['CONSTRAINT_NAME']]['REFERENCED_COLUMNS'][] = $row['REFERENCED_COLUMN_NAME'];

        }

        return $foreignKeys;

    }