1
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_TABLES;
2
TABLE_ID NAME FLAG N_COLS SPACE
4
12 SYS_FOREIGN_COLS 0 7 0
5
13 SYS_REPLICATION_LOG 0 5 0
6
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_INDEXES;
7
INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE
9
12 FOR_IND 11 0 1 303 0
10
13 REF_IND 11 0 1 304 0
11
14 ID_IND 12 3 2 305 0
12
15 ID_IND 13 3 1 307 0
13
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_COLUMNS;
14
TABLE_ID NAME POS MTYPE PRTYPE LEN
16
11 FOR_NAME 1 1 2949124 0
17
11 REF_NAME 2 1 2949124 0
21
12 FOR_COL_NAME 2 1 2949124 0
22
12 REF_COL_NAME 3 1 2949124 0
24
13 MESSAGE 1 5 4129792 0
25
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FIELDS;
33
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
34
ID FOR_NAME REF_NAME N_COLS TYPE
35
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
36
ID FOR_COL_NAME REF_COL_NAME POS
37
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS;
38
TABLE_ID NAME STATS_INITIALIZED NUM_ROWS CLUST_INDEX_SIZE OTHER_INDEX_SIZE MODIFIED_COUNTER AUTOINC HANDLES_OPENED
39
11 SYS_FOREIGN Uninitialized 0 0 0 0 0 0
40
12 SYS_FOREIGN_COLS Uninitialized 0 0 0 0 0 0
41
13 SYS_REPLICATION_LOG Uninitialized 0 0 0 0 0 0
42
CREATE TABLE parent (id INT NOT NULL,
43
PRIMARY KEY (id)) ENGINE=INNODB;
44
CREATE TABLE child (id INT, parent_id INT,
45
INDEX par_ind (parent_id),
46
CONSTRAINT constraint_test
47
FOREIGN KEY (parent_id) REFERENCES parent(id)
48
ON DELETE CASCADE) ENGINE=INNODB;
49
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
50
ID FOR_NAME REF_NAME N_COLS TYPE
51
test/constraint_test test/child test/parent 1 1
52
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
53
ID FOR_COL_NAME REF_COL_NAME POS
54
test/constraint_test parent_id id 0
55
INSERT INTO parent VALUES(1);
56
SELECT name, num_rows, handles_opened
57
FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS
58
WHERE name LIKE "%parent";
59
name num_rows handles_opened
61
SELECT NAME, FLAG, N_COLS, SPACE FROM DATA_DICTIONARY.INNODB_SYS_TABLES;
62
NAME FLAG N_COLS SPACE
64
SYS_FOREIGN_COLS 0 7 0
65
SYS_REPLICATION_LOG 0 5 0
69
from DATA_DICTIONARY.INNODB_SYS_INDEXES
70
WHERE table_id In (SELECT table_id from
71
DATA_DICTIONARY.INNODB_SYS_TABLES
72
WHERE name LIKE "%parent%");
76
from DATA_DICTIONARY.INNODB_SYS_INDEXES
77
WHERE table_id In (SELECT table_id from
78
DATA_DICTIONARY.INNODB_SYS_TABLES
79
WHERE name LIKE "%child%");
83
SELECT name, pos, mtype, len
84
from DATA_DICTIONARY.INNODB_SYS_COLUMNS
85
WHERE table_id In (SELECT table_id from
86
DATA_DICTIONARY.INNODB_SYS_TABLES
87
WHERE name LIKE "%child%");
93
CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
94
PRIMARY KEY (id, newid)) ENGINE=INNODB;
95
CREATE TABLE child (id INT, parent_id INT,
96
INDEX par_ind (parent_id),
97
CONSTRAINT constraint_test
98
FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
99
ON DELETE CASCADE) ENGINE=INNODB;
100
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
101
ID FOR_NAME REF_NAME N_COLS TYPE
102
test/constraint_test test/child test/parent 2 1
103
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
104
ID FOR_COL_NAME REF_COL_NAME POS
105
test/constraint_test id id 0
106
test/constraint_test parent_id newid 1
107
INSERT INTO parent VALUES(1, 9);
108
SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
111
SELECT name, num_rows, handles_opened
112
FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS
113
WHERE name LIKE "%parent";
114
name num_rows handles_opened