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