mysql - primary - ¿Cómo encontrar todas las tablas que tienen claves externas que hacen referencia a table.column en particular y tienen valores para esas claves externas?
llaves primarias y foraneas en sql server (7)
Aqui tienes:
USE information_schema;
SELECT *
FROM
KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = ''X''
AND REFERENCED_COLUMN_NAME = ''X_id'';
Si tiene varias bases de datos con tablas / nombres de columna similares, también puede limitar su consulta a una base de datos en particular:
SELECT *
FROM
KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = ''X''
AND REFERENCED_COLUMN_NAME = ''X_id''
AND TABLE_SCHEMA = ''your_database_name'';
Tengo una tabla cuya clave principal está referenciada en varias otras tablas como una clave externa. Por ejemplo:
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`)
)
Ahora, no sé cuántas tablas hay en la base de datos que contienen claves externas en X como tablas Y y Z. ¿Hay alguna consulta SQL que pueda usar para devolver:
- Una lista de tablas que tienen claves foráneas en X
- Y cuál de esas tablas realmente tiene valores en la clave externa
Escribí un poco de bash onliner que puedes escribir en un script para obtener un resultado amigable:
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/,/./''
Así que la ejecución: mysql_references_to transaccion
(donde transaccion es un nombre de tabla aleatorio) da una salida como esta:
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
...
Esta solución no solo mostrará todas las relaciones, sino también el nombre de la restricción, que se requiere en algunos casos (por ejemplo, restricción de caída):
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;
Si desea verificar las tablas en una base de datos específica, agregue lo siguiente:
AND table_schema = ''database_name'';
Listado de todas las claves foráneas en un db incluyendo descripción
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'';
restringir a una columna específica en una tabla de la tabla
AND i1.table_name = ''target_tb_name'' AND i1.column_name = ''target_col_name''
Más fácil:
1. Abre phpMyAdmin
2. A la izquierda, haga clic en la base de datos nombre
3. En la esquina superior derecha, encuentra la pestaña "Diseñador"
Todas las restricciones se mostrarán allí.
MySQL 5.5 Reference Manual: "InnoDB y restricciones forenses clave"
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;
Puede encontrar toda la información relacionada con el esquema en la tabla de esquema de información con nombre inteligente.
Es posible que desee consultar la tabla KEY_COLUMN_USAGE
y KEY_COLUMN_USAGE
. El primero te dice a qué tablas hacen referencia otros usuarios; este último le dirá cómo se relacionan sus campos.