mysql主外來鍵依賴關係

psufnxk2000發表於2014-10-30
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章