|
Thanks a lot! Reproducible on 10.3, 10.4:
CREATE TABLE author (
|
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
name VARCHAR(100) NOT NULL
|
) ENGINE = InnoDB;
|
|
CREATE TABLE book (
|
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
title VARCHAR(200) NOT NULL,
|
author_id SMALLINT UNSIGNED NOT NULL,
|
CONSTRAINT `fk_b` FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE ON UPDATE RESTRICT
|
) ENGINE = InnoDB;
|
|
SELECT k.TABLE_NAME,r.DELETE_RULE
|
FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k
|
USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME);
|
|
SELECT k.TABLE_NAME,r.DELETE_RULE
|
FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k
|
ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME;
|
MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE
|
-> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k
|
-> USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME);
|
Empty set (0.037 sec)
|
|
MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE
|
-> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k
|
-> ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME;
|
+------------+-------------+
|
| TABLE_NAME | DELETE_RULE |
|
+------------+-------------+
|
| book | CASCADE |
|
+------------+-------------+
|
1 row in set (0.033 sec)
|
|
MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 35.09,
|
"table": {
|
"table_name": "r",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0521,
|
"r_filtered": 100,
|
"open_full_table": true,
|
"scanned_databases": "all"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "k",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"r_rows": 71,
|
"r_total_time_ms": 0.0383,
|
"r_filtered": 100,
|
"open_full_table": true,
|
"scanned_databases": "all"
|
},
|
"buffer_type": "flat",
|
"buffer_size": "2Kb",
|
"join_type": "BNL",
|
"attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`",
|
"r_filtered": 0
|
}
|
}
|
}
|
1 row in set (0.037 sec)
|
|
MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 36.172,
|
"table": {
|
"table_name": "r",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0514,
|
"r_filtered": 100,
|
"open_full_table": true,
|
"scanned_databases": "all"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "k",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"r_rows": 71,
|
"r_total_time_ms": 0.0762,
|
"r_filtered": 100,
|
"open_full_table": true,
|
"scanned_databases": "all"
|
},
|
"buffer_type": "flat",
|
"buffer_size": "22Kb",
|
"join_type": "BNL",
|
"attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`",
|
"r_filtered": 1.4085
|
}
|
}
|
}
|
1 row in set (0.038 sec)
|
|
|