I have a table whose primary key is referenced in several other tables as a foreign key. For example:
CREATE TABLE `X` (
`X_id` int NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`X_id`)
)
CREATE TABLE `Y` (
`Y_id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`X_id` int DEFAULT NULL,
PRIMARY KEY (`Y_id`),
CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
)
CREATE TABLE `Z` (
`Z_id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`X_id` int DEFAULT NULL,
PRIMARY KEY (`Z_id`),
CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
)
Now, I don't know how many tables there are in the database that contain foreign keys into X like tables Y and Z. Is there a SQL query that I can use to return:
This question is related to
mysql
foreign-keys
MySQL 5.5 Reference Manual: "InnoDB and FOREIGN KEY Constraints"
SELECT
ke.REFERENCED_TABLE_SCHEMA parentSchema,
ke.referenced_table_name parentTable,
ke.REFERENCED_COLUMN_NAME parentColumnName,
ke.TABLE_SCHEMA ChildSchema,
ke.table_name childTable,
ke.COLUMN_NAME ChildColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
AND ke.REFERENCED_COLUMN_NAME = 'ci_id' ## Find Foreign Keys linked to this Primary Key
ORDER BY
ke.referenced_table_name;
Easiest:
1. Open phpMyAdmin
2. On the left click database name
3. On the top right corner find "Designer" tab
All constraints will be shown there.
I wrote a little bash onliner that you can write to a script to get a friendly output:
mysql_references_to:
mysql -uUSER -pPASS -A DB_NAME -se "USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '$1' AND REFERENCED_COLUMN_NAME = 'id'\G" | sed 's/^[ \t]*//;s/[ \t]*$//' |egrep "\<TABLE_NAME|\<COLUMN_NAME" |sed 's/TABLE_NAME: /./g' |sed 's/COLUMN_NAME: //g' | paste -sd "," -| tr '.' '\n' |sed 's/,$//' |sed 's/,/./'
So the execution: mysql_references_to transaccion
(where transaccion is a random table name) gives an output like this:
carrito_transaccion.transaccion_id
comanda_detalle.transaccion_id
comanda_detalle_devolucion.transaccion_positiva_id
comanda_detalle_devolucion.transaccion_negativa_id
comanda_transaccion.transaccion_id
cuenta_operacion.transaccion_id
...
You can find all schema related information in the wisely named information_schema
table.
You might want to check the table REFERENTIAL_CONSTRAINTS
and KEY_COLUMN_USAGE
. The former tells you which tables are referenced by others; the latter will tell you how their fields are related.
This solution will not only display all relations but also the constraint name, which is required in some cases (e.g. drop constraint):
SELECT
CONCAT(table_name, '.', column_name) AS 'foreign key',
CONCAT(referenced_table_name, '.', referenced_column_name) AS 'references',
constraint_name AS 'constraint name'
FROM
information_schema.key_column_usage
WHERE
referenced_table_name IS NOT NULL;
If you want to check tables in a specific database, add the following:
AND table_schema = 'database_name';
Listing all foreign keys in a db including description
SELECT
i1.CONSTRAINT_NAME, i1.TABLE_NAME,i1.COLUMN_NAME,
i1.REFERENCED_TABLE_SCHEMA,i1.REFERENCED_TABLE_NAME, i1.REFERENCED_COLUMN_NAME,
i2.UPDATE_RULE, i2.DELETE_RULE
FROM
information_schema.KEY_COLUMN_USAGE AS i1
INNER JOIN
information_schema.REFERENTIAL_CONSTRAINTS AS i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.REFERENCED_TABLE_NAME IS NOT NULL
AND i1.TABLE_SCHEMA ='db_name';
restricting to a specific column in a table table
AND i1.table_name = 'target_tb_name' AND i1.column_name = 'target_col_name'
Here you go:
USE information_schema;
SELECT *
FROM
KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'X'
AND REFERENCED_COLUMN_NAME = 'X_id';
If you have multiple databases with similar tables/column names you may also wish to limit your query to a particular database:
SELECT *
FROM
KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'X'
AND REFERENCED_COLUMN_NAME = 'X_id'
AND TABLE_SCHEMA = 'your_database_name';
Source: Stackoverflow.com