mysql主外键依赖关系

查看主外键的依赖关系：

在oracle中可以通过 DBA_DEPENDENCIES查看得到。 在mysql中也可以查看

通过查看 information_schema.  key_column_usage  

MariaDB [test]> create table t1 ( id int primary key,name varchar(10));

Query OK, 0 rows affected (0.36 sec)

MariaDB [test]> create table t2 (id int,score int);

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> Alter table t2 add foreign key (id) references t1(id);

Query OK, 0 rows affected (0.15 sec)               

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table t2;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                        |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `id` int(11) DEFAULT NULL,

  `score` int(11) DEFAULT NULL,

  KEY `id` (`id`),

  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [information_schema]> select * from key_column_usage where 

    -> REFERENCED_COLUMN_NAME='ID' and 

    -> REFERENCED_TABLE_NAME='T1'

    -> ;

+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |

+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+

| def                | test              | t2_ibfk_1       | def           | test         | t2         | id          |                1 |                             1 | test                    | t1                    | id                     |

+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+

1 row in set (0.01 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-1315196/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-1315196/