1
SHOW TABLES FROM information_schema LIKE 'KEY_COLUMN_USAGE';
2
Tables_in_information_schema (KEY_COLUMN_USAGE)
4
#######################################################################
5
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6
#######################################################################
7
DROP VIEW IF EXISTS test.v1;
8
DROP PROCEDURE IF EXISTS test.p1;
9
DROP FUNCTION IF EXISTS test.f1;
10
CREATE VIEW test.v1 AS SELECT * FROM information_schema.KEY_COLUMN_USAGE;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.KEY_COLUMN_USAGE;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.KEY_COLUMN_USAGE;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.KEY_COLUMN_USAGE;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout
28
#########################################################################
29
DESCRIBE information_schema.KEY_COLUMN_USAGE;
30
Field Type Null Key Default Extra
31
CONSTRAINT_CATALOG varchar(512) YES NULL
32
CONSTRAINT_SCHEMA varchar(64) NO
33
CONSTRAINT_NAME varchar(64) NO
34
TABLE_CATALOG varchar(512) YES NULL
35
TABLE_SCHEMA varchar(64) NO
36
TABLE_NAME varchar(64) NO
37
COLUMN_NAME varchar(64) NO
38
ORDINAL_POSITION bigint(10) NO 0
39
POSITION_IN_UNIQUE_CONSTRAINT bigint(10) YES NULL
40
REFERENCED_TABLE_SCHEMA varchar(64) YES NULL
41
REFERENCED_TABLE_NAME varchar(64) YES NULL
42
REFERENCED_COLUMN_NAME varchar(64) YES NULL
43
SHOW CREATE TABLE information_schema.KEY_COLUMN_USAGE;
45
KEY_COLUMN_USAGE CREATE TEMPORARY TABLE `KEY_COLUMN_USAGE` (
46
`CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
47
`CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
48
`CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
49
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
50
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
51
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
52
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
53
`ORDINAL_POSITION` bigint(10) NOT NULL DEFAULT '0',
54
`POSITION_IN_UNIQUE_CONSTRAINT` bigint(10) DEFAULT NULL,
55
`REFERENCED_TABLE_SCHEMA` varchar(64) DEFAULT NULL,
56
`REFERENCED_TABLE_NAME` varchar(64) DEFAULT NULL,
57
`REFERENCED_COLUMN_NAME` varchar(64) DEFAULT NULL
58
) ENGINE=MEMORY DEFAULT CHARSET=utf8
59
SHOW COLUMNS FROM information_schema.KEY_COLUMN_USAGE;
60
Field Type Null Key Default Extra
61
CONSTRAINT_CATALOG varchar(512) YES NULL
62
CONSTRAINT_SCHEMA varchar(64) NO
63
CONSTRAINT_NAME varchar(64) NO
64
TABLE_CATALOG varchar(512) YES NULL
65
TABLE_SCHEMA varchar(64) NO
66
TABLE_NAME varchar(64) NO
67
COLUMN_NAME varchar(64) NO
68
ORDINAL_POSITION bigint(10) NO 0
69
POSITION_IN_UNIQUE_CONSTRAINT bigint(10) YES NULL
70
REFERENCED_TABLE_SCHEMA varchar(64) YES NULL
71
REFERENCED_TABLE_NAME varchar(64) YES NULL
72
REFERENCED_COLUMN_NAME varchar(64) YES NULL
73
SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog,
74
table_schema, table_name, column_name
75
FROM information_schema.key_column_usage
76
WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL;
77
constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name
78
########################################################################################
79
# Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information
80
########################################################################################
81
DROP DATABASE IF EXISTS db_datadict;
82
CREATE DATABASE db_datadict;
83
DROP USER 'testuser1'@'localhost';
84
CREATE USER 'testuser1'@'localhost';
85
DROP USER 'testuser2'@'localhost';
86
CREATE USER 'testuser2'@'localhost';
89
(f1 INT NOT NULL, PRIMARY KEY(f1),
90
f2 INT, INDEX f2_ind(f2))
91
ENGINE = <engine_type>;
92
GRANT SELECT ON t1_1 to 'testuser1'@'localhost';
94
(f1 INT NOT NULL, PRIMARY KEY(f1),
95
f2 INT, INDEX f2_ind(f2))
96
ENGINE = <engine_type>;
97
GRANT SELECT ON t1_2 to 'testuser2'@'localhost';
98
SELECT * FROM information_schema.key_column_usage
99
WHERE table_name LIKE 't1_%'
100
ORDER BY constraint_catalog, constraint_schema, constraint_name,
101
table_catalog, table_schema, table_name, ordinal_position;
102
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
103
NULL db_datadict PRIMARY NULL db_datadict t1_1 f1 1 NULL NULL NULL NULL
104
NULL db_datadict PRIMARY NULL db_datadict t1_2 f1 1 NULL NULL NULL NULL
105
# Establish connection testuser1 (user=testuser1)
106
SELECT * FROM information_schema.key_column_usage
107
WHERE table_name LIKE 't1_%'
108
ORDER BY constraint_catalog, constraint_schema, constraint_name,
109
table_catalog, table_schema, table_name, ordinal_position;
110
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
111
NULL db_datadict PRIMARY NULL db_datadict t1_1 f1 1 NULL NULL NULL NULL
112
# Establish connection testuser2 (user=testuser2)
113
SELECT * FROM information_schema.key_column_usage
114
WHERE table_name LIKE 't1_%'
115
ORDER BY constraint_catalog, constraint_schema, constraint_name,
116
table_catalog, table_schema, table_name, ordinal_position;
117
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
118
NULL db_datadict PRIMARY NULL db_datadict t1_2 f1 1 NULL NULL NULL NULL
119
# Switch to connection default and close connections testuser1, testuser2
120
DROP USER 'testuser1'@'localhost';
121
DROP USER 'testuser2'@'localhost';
124
DROP DATABASE IF EXISTS db_datadict;
125
########################################################################################
126
# Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications
127
########################################################################################
128
DROP DATABASE IF EXISTS db_datadict;
129
DROP TABLE IF EXISTS test.t1_my_table;
130
CREATE DATABASE db_datadict;
131
SELECT table_name FROM information_schema.key_column_usage
132
WHERE table_name LIKE 't1_my_table%';
134
CREATE TABLE test.t1_my_table
135
(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
136
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
137
ENGINE = <engine_type>;
138
SELECT * FROM information_schema.key_column_usage
139
WHERE table_name = 't1_my_table';
140
CONSTRAINT_CATALOG NULL
141
CONSTRAINT_SCHEMA test
142
CONSTRAINT_NAME PRIMARY
145
TABLE_NAME t1_my_table
148
POSITION_IN_UNIQUE_CONSTRAINT NULL
149
REFERENCED_TABLE_SCHEMA NULL
150
REFERENCED_TABLE_NAME NULL
151
REFERENCED_COLUMN_NAME NULL
152
CONSTRAINT_CATALOG NULL
153
CONSTRAINT_SCHEMA test
154
CONSTRAINT_NAME PRIMARY
157
TABLE_NAME t1_my_table
160
POSITION_IN_UNIQUE_CONSTRAINT NULL
161
REFERENCED_TABLE_SCHEMA NULL
162
REFERENCED_TABLE_NAME NULL
163
REFERENCED_COLUMN_NAME NULL
164
SELECT DISTINCT table_name FROM information_schema.key_column_usage
165
WHERE table_name LIKE 't1_my_table%';
168
RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
169
SELECT DISTINCT table_name FROM information_schema.key_column_usage
170
WHERE table_name LIKE 't1_my_table%';
173
SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
174
WHERE table_name = 't1_my_tablex';
175
table_schema table_name
177
RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
178
SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
179
WHERE table_name = 't1_my_tablex';
180
table_schema table_name
181
db_datadict t1_my_tablex
182
SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
183
WHERE table_name = 't1_my_tablex'
184
ORDER BY table_name, column_name;
185
table_name column_name
188
ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
189
SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
190
WHERE table_name = 't1_my_tablex'
191
ORDER BY table_name, column_name;
192
table_name column_name
194
t1_my_tablex first_col
195
SELECT constraint_schema, constraint_name, table_schema,
196
table_name, column_name, ordinal_position
197
FROM information_schema.key_column_usage
198
WHERE table_name = 't1_my_tablex'
199
ORDER BY constraint_schema, constraint_name, table_schema,
200
table_name, ordinal_position;
201
constraint_schema constraint_name table_schema table_name column_name ordinal_position
202
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
203
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
204
CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
205
SELECT constraint_schema, constraint_name, table_schema,
206
table_name, column_name, ordinal_position
207
FROM information_schema.key_column_usage
208
WHERE table_name = 't1_my_tablex'
209
ORDER BY constraint_schema, constraint_name, table_schema,
210
table_name, ordinal_position;
211
constraint_schema constraint_name table_schema table_name column_name ordinal_position
212
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
213
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
214
DROP INDEX f2 ON db_datadict.t1_my_tablex;
215
SELECT constraint_schema, constraint_name, table_schema,
216
table_name, column_name, ordinal_position
217
FROM information_schema.key_column_usage
218
WHERE table_name = 't1_my_tablex'
219
ORDER BY constraint_schema, constraint_name, table_schema,
220
table_name, ordinal_position;
221
constraint_schema constraint_name table_schema table_name column_name ordinal_position
222
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
223
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
224
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
225
SELECT constraint_schema, constraint_name, table_schema,
226
table_name, column_name, ordinal_position
227
FROM information_schema.key_column_usage
228
WHERE table_name = 't1_my_tablex'
229
ORDER BY constraint_schema, constraint_name, table_schema,
230
table_name, ordinal_position;
231
constraint_schema constraint_name table_schema table_name column_name ordinal_position
232
db_datadict f2 db_datadict t1_my_tablex f2 1
233
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
234
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
235
DROP INDEX f2 ON db_datadict.t1_my_tablex;
236
SELECT constraint_schema, constraint_name, table_schema,
237
table_name, column_name, ordinal_position
238
FROM information_schema.key_column_usage
239
WHERE table_name = 't1_my_tablex'
240
ORDER BY constraint_schema, constraint_name, table_schema,
241
table_name, ordinal_position;
242
constraint_schema constraint_name table_schema table_name column_name ordinal_position
243
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
244
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
245
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
246
SELECT constraint_schema, constraint_name, table_schema,
247
table_name, column_name, ordinal_position
248
FROM information_schema.key_column_usage
249
WHERE table_name = 't1_my_tablex'
250
ORDER BY constraint_schema, constraint_name, table_schema,
251
table_name, ordinal_position;
252
constraint_schema constraint_name table_schema table_name column_name ordinal_position
253
db_datadict my_idx db_datadict t1_my_tablex f2 1
254
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
255
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
256
DROP INDEX my_idx ON db_datadict.t1_my_tablex;
257
SELECT constraint_schema, constraint_name, table_schema,
258
table_name, column_name, ordinal_position
259
FROM information_schema.key_column_usage
260
WHERE table_name = 't1_my_tablex'
261
ORDER BY constraint_schema, constraint_name, table_schema,
262
table_name, ordinal_position;
263
constraint_schema constraint_name table_schema table_name column_name ordinal_position
264
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
265
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
266
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
267
SELECT constraint_schema, constraint_name, table_schema,
268
table_name, column_name, ordinal_position
269
FROM information_schema.key_column_usage
270
WHERE table_name = 't1_my_tablex'
271
ORDER BY constraint_schema, constraint_name, table_schema,
272
table_name, ordinal_position;
273
constraint_schema constraint_name table_schema table_name column_name ordinal_position
274
db_datadict my_idx db_datadict t1_my_tablex f4 1
275
db_datadict my_idx db_datadict t1_my_tablex first_col 2
276
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
277
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
278
SELECT constraint_schema, constraint_name, table_schema,
279
table_name, column_name, ordinal_position
280
FROM information_schema.key_column_usage
281
WHERE table_name = 't1_my_tablex'
282
ORDER BY constraint_schema, constraint_name, table_schema,
283
table_name, ordinal_position;
284
constraint_schema constraint_name table_schema table_name column_name ordinal_position
285
db_datadict my_idx db_datadict t1_my_tablex f4 1
286
db_datadict my_idx db_datadict t1_my_tablex first_col 2
287
db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
288
db_datadict PRIMARY db_datadict t1_my_tablex f2 2
289
ALTER TABLE db_datadict.t1_my_tablex
290
DROP COLUMN first_col;
291
SELECT constraint_schema, constraint_name, table_schema,
292
table_name, column_name, ordinal_position
293
FROM information_schema.key_column_usage
294
WHERE table_name = 't1_my_tablex'
295
ORDER BY constraint_schema, constraint_name, table_schema,
296
table_name, ordinal_position;
297
constraint_schema constraint_name table_schema table_name column_name ordinal_position
298
db_datadict my_idx db_datadict t1_my_tablex f4 1
299
db_datadict PRIMARY db_datadict t1_my_tablex f2 1
300
SELECT table_name, column_name
301
FROM information_schema.key_column_usage
302
WHERE table_name = 't1_my_tablex'
303
ORDER BY table_name, column_name;
304
table_name column_name
307
DROP TABLE db_datadict.t1_my_tablex;
308
SELECT table_name, column_name
309
FROM information_schema.key_column_usage
310
WHERE table_name = 't1_my_tablex';
311
table_name column_name
312
SELECT table_name FROM information_schema.key_column_usage
313
WHERE table_name = 't1_my_tablex';
315
CREATE TABLE db_datadict.t1_my_tablex
316
ENGINE = <engine_type> AS
318
SELECT table_name FROM information_schema.key_column_usage
319
WHERE table_name = 't1_my_tablex';
321
ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
322
SELECT table_name FROM information_schema.key_column_usage
323
WHERE table_name = 't1_my_tablex';
326
SELECT table_name FROM information_schema.key_column_usage
327
WHERE table_name = 't1_my_tablex';
330
DROP DATABASE db_datadict;
331
SELECT table_name FROM information_schema.key_column_usage
332
WHERE table_name = 't1_my_tablex';
334
########################################################################
335
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
336
# DDL on INFORMATION_SCHEMA table are not supported
337
########################################################################
338
DROP DATABASE IF EXISTS db_datadict;
339
DROP TABLE IF EXISTS db_datadict.t1;
340
CREATE DATABASE db_datadict;
341
CREATE TABLE db_datadict.t1 (f1 BIGINT)
342
ENGINE = <engine_type>;
343
INSERT INTO information_schema.key_column_usage
344
(constraint_schema, constraint_name, table_name)
345
VALUES ( 'mysql', 'primary', 'db');
346
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
347
INSERT INTO information_schema.key_column_usage
348
SELECT * FROM information_schema.key_column_usage;
349
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
350
UPDATE information_schema.key_column_usage
351
SET table_name = 'db1' WHERE constraint_name = 'primary';
352
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
353
DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
354
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
355
TRUNCATE information_schema.key_column_usage;
356
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
357
CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
358
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
359
ALTER TABLE information_schema.key_column_usage ADD f1 INT;
360
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
361
DROP TABLE information_schema.key_column_usage;
362
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
363
ALTER TABLE information_schema.key_column_usage
364
RENAME db_datadict.key_column_usage;
365
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
366
ALTER TABLE information_schema.key_column_usage
367
RENAME information_schema.xkey_column_usage;
368
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
369
DROP TABLE db_datadict.t1;
370
DROP DATABASE db_datadict;
added
removed
mysql-test/suite/funcs_1/r/is_key_column_usage.result
