Debian Codegraph @

mysql-test/suite/funcs_1/r/is_table_constraints.result

raw
1 SHOW TABLES FROM information_schema LIKE 'TABLE_CONSTRAINTS';
2 Tables_in_information_schema (TABLE_CONSTRAINTS)
3 TABLE_CONSTRAINTS
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.TABLE_CONSTRAINTS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLE_CONSTRAINTS;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.TABLE_CONSTRAINTS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.TABLE_CONSTRAINTS;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout
28 #########################################################################
29 DESCRIBE information_schema.TABLE_CONSTRAINTS;
30 Field Type Null Key Default Extra
31 CONSTRAINT_CATALOG varchar(64) NO NULL
32 CONSTRAINT_SCHEMA varchar(64) NO NULL
33 CONSTRAINT_NAME varchar(64) YES NULL
34 TABLE_SCHEMA varchar(64) NO NULL
35 TABLE_NAME varchar(64) NO NULL
36 CONSTRAINT_TYPE varchar(11) NO
37 ENFORCED varchar(3) NO
38 SHOW CREATE TABLE information_schema.TABLE_CONSTRAINTS;
39 View Create View character_set_client collation_connection
40 TABLE_CONSTRAINTS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TABLE_CONSTRAINTS` AS select `cat`.`name` AS `CONSTRAINT_CATALOG`,`sch`.`name` AS `CONSTRAINT_SCHEMA`,`constraints`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`constraints`.`CONSTRAINT_TYPE` AS `CONSTRAINT_TYPE`,`constraints`.`ENFORCED` AS `ENFORCED` from (((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join lateral (select `idx`.`name` AS `CONSTRAINT_NAME`,if((`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`idx`.`type`) AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` from `mysql`.`indexes` `idx` where ((`idx`.`table_id` = `tbl`.`id`) and (`idx`.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(`tbl`.`hidden`,`idx`.`hidden`,`idx`.`options`))) union all select (`fk`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_NAME`,'FOREIGN KEY' AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` from `mysql`.`foreign_keys` `fk` where (`fk`.`table_id` = `tbl`.`id`) union all select `cc`.`name` AS `CONSTRAINT_NAME`,'CHECK' AS `CONSTRAINT_TYPE`,`cc`.`enforced` AS `ENFORCED` from `mysql`.`check_constraints` `cc` where (`cc`.`table_id` = `tbl`.`id`)) `constraints`) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`))) utf8mb3 utf8mb3_general_ci
41 SHOW COLUMNS FROM information_schema.TABLE_CONSTRAINTS;
42 Field Type Null Key Default Extra
43 CONSTRAINT_CATALOG varchar(64) NO NULL
44 CONSTRAINT_SCHEMA varchar(64) NO NULL
45 CONSTRAINT_NAME varchar(64) YES NULL
46 TABLE_SCHEMA varchar(64) NO NULL
47 TABLE_NAME varchar(64) NO NULL
48 CONSTRAINT_TYPE varchar(11) NO
49 ENFORCED varchar(3) NO
50 SELECT constraint_catalog, constraint_schema, constraint_name,
51 table_schema, table_name
52 FROM information_schema.table_constraints
53 WHERE constraint_catalog IS NOT NULL AND
54 table_name != 'ndb_binlog_index'
55 ORDER BY table_schema,table_name, constraint_name;
56 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME
57 def mtr PRIMARY mtr asserted_test_suppressions
58 def mtr PRIMARY mtr test_ignored_global_suppressions
59 def mysql PRIMARY mysql columns_priv
60 def mysql PRIMARY mysql component
61 def mysql PRIMARY mysql db
62 def mysql PRIMARY mysql default_roles
63 def mysql PRIMARY mysql engine_cost
64 def mysql PRIMARY mysql func
65 def mysql PRIMARY mysql global_grants
66 def mysql PRIMARY mysql gtid_executed
67 def mysql name mysql help_category
68 def mysql PRIMARY mysql help_category
69 def mysql name mysql help_keyword
70 def mysql PRIMARY mysql help_keyword
71 def mysql PRIMARY mysql help_relation
72 def mysql name mysql help_topic
73 def mysql PRIMARY mysql help_topic
74 def mysql PRIMARY mysql innodb_index_stats
75 def mysql PRIMARY mysql innodb_table_stats
76 def mysql PRIMARY mysql password_history
77 def mysql PRIMARY mysql plugin
78 def mysql PRIMARY mysql procs_priv
79 def mysql PRIMARY mysql proxies_priv
80 def mysql PRIMARY mysql replication_asynchronous_connection_failover
81 def mysql PRIMARY mysql replication_asynchronous_connection_failover_managed
82 def mysql PRIMARY mysql replication_group_configuration_version
83 def mysql PRIMARY mysql replication_group_member_actions
84 def mysql PRIMARY mysql role_edges
85 def mysql PRIMARY mysql server_cost
86 def mysql PRIMARY mysql servers
87 def mysql PRIMARY mysql slave_master_info
88 def mysql PRIMARY mysql slave_relay_log_info
89 def mysql PRIMARY mysql slave_worker_info
90 def mysql PRIMARY mysql tables_priv
91 def mysql PRIMARY mysql time_zone
92 def mysql PRIMARY mysql time_zone_leap_second
93 def mysql PRIMARY mysql time_zone_name
94 def mysql PRIMARY mysql time_zone_transition
95 def mysql PRIMARY mysql time_zone_transition_type
96 def mysql PRIMARY mysql user
97 def performance_schema ACCOUNT performance_schema accounts
98 def performance_schema PRIMARY performance_schema cond_instances
99 def performance_schema PRIMARY performance_schema data_lock_waits
100 def performance_schema PRIMARY performance_schema data_locks
101 def performance_schema PRIMARY performance_schema error_log
102 def performance_schema ACCOUNT performance_schema events_errors_summary_by_account_by_error
103 def performance_schema HOST performance_schema events_errors_summary_by_host_by_error
104 def performance_schema THREAD_ID performance_schema events_errors_summary_by_thread_by_error
105 def performance_schema USER performance_schema events_errors_summary_by_user_by_error
106 def performance_schema ERROR_NUMBER performance_schema events_errors_summary_global_by_error
107 def performance_schema PRIMARY performance_schema events_stages_current
108 def performance_schema PRIMARY performance_schema events_stages_history
109 def performance_schema ACCOUNT performance_schema events_stages_summary_by_account_by_event_name
110 def performance_schema HOST performance_schema events_stages_summary_by_host_by_event_name
111 def performance_schema PRIMARY performance_schema events_stages_summary_by_thread_by_event_name
112 def performance_schema USER performance_schema events_stages_summary_by_user_by_event_name
113 def performance_schema PRIMARY performance_schema events_stages_summary_global_by_event_name
114 def performance_schema PRIMARY performance_schema events_statements_current
115 def performance_schema SCHEMA_NAME performance_schema events_statements_histogram_by_digest
116 def performance_schema PRIMARY performance_schema events_statements_histogram_global
117 def performance_schema PRIMARY performance_schema events_statements_history
118 def performance_schema ACCOUNT performance_schema events_statements_summary_by_account_by_event_name
119 def performance_schema SCHEMA_NAME performance_schema events_statements_summary_by_digest
120 def performance_schema HOST performance_schema events_statements_summary_by_host_by_event_name
121 def performance_schema PRIMARY performance_schema events_statements_summary_by_program
122 def performance_schema PRIMARY performance_schema events_statements_summary_by_thread_by_event_name
123 def performance_schema USER performance_schema events_statements_summary_by_user_by_event_name
124 def performance_schema PRIMARY performance_schema events_statements_summary_global_by_event_name
125 def performance_schema PRIMARY performance_schema events_transactions_current
126 def performance_schema PRIMARY performance_schema events_transactions_history
127 def performance_schema ACCOUNT performance_schema events_transactions_summary_by_account_by_event_name
128 def performance_schema HOST performance_schema events_transactions_summary_by_host_by_event_name
129 def performance_schema PRIMARY performance_schema events_transactions_summary_by_thread_by_event_name
130 def performance_schema USER performance_schema events_transactions_summary_by_user_by_event_name
131 def performance_schema PRIMARY performance_schema events_transactions_summary_global_by_event_name
132 def performance_schema PRIMARY performance_schema events_waits_current
133 def performance_schema PRIMARY performance_schema events_waits_history
134 def performance_schema ACCOUNT performance_schema events_waits_summary_by_account_by_event_name
135 def performance_schema HOST performance_schema events_waits_summary_by_host_by_event_name
136 def performance_schema PRIMARY performance_schema events_waits_summary_by_instance
137 def performance_schema PRIMARY performance_schema events_waits_summary_by_thread_by_event_name
138 def performance_schema USER performance_schema events_waits_summary_by_user_by_event_name
139 def performance_schema PRIMARY performance_schema events_waits_summary_global_by_event_name
140 def performance_schema PRIMARY performance_schema file_instances
141 def performance_schema PRIMARY performance_schema file_summary_by_event_name
142 def performance_schema PRIMARY performance_schema file_summary_by_instance
143 def performance_schema PRIMARY performance_schema global_status
144 def performance_schema PRIMARY performance_schema global_variables
145 def performance_schema PRIMARY performance_schema host_cache
146 def performance_schema HOST performance_schema hosts
147 def performance_schema ACCOUNT performance_schema memory_summary_by_account_by_event_name
148 def performance_schema HOST performance_schema memory_summary_by_host_by_event_name
149 def performance_schema PRIMARY performance_schema memory_summary_by_thread_by_event_name
150 def performance_schema USER performance_schema memory_summary_by_user_by_event_name
151 def performance_schema PRIMARY performance_schema memory_summary_global_by_event_name
152 def performance_schema PRIMARY performance_schema metadata_locks
153 def performance_schema PRIMARY performance_schema mutex_instances
154 def performance_schema OBJECT performance_schema objects_summary_global_by_type
155 def performance_schema PRIMARY performance_schema persisted_variables
156 def performance_schema OWNER_THREAD_ID performance_schema prepared_statements_instances
157 def performance_schema PRIMARY performance_schema prepared_statements_instances
158 def performance_schema PRIMARY performance_schema processlist
159 def performance_schema PRIMARY performance_schema replication_applier_configuration
160 def performance_schema PRIMARY performance_schema replication_applier_status
161 def performance_schema PRIMARY performance_schema replication_applier_status_by_coordinator
162 def performance_schema PRIMARY performance_schema replication_applier_status_by_worker
163 def performance_schema PRIMARY performance_schema replication_connection_configuration
164 def performance_schema PRIMARY performance_schema replication_connection_status
165 def performance_schema PRIMARY performance_schema rwlock_instances
166 def performance_schema PRIMARY performance_schema session_account_connect_attrs
167 def performance_schema PRIMARY performance_schema session_connect_attrs
168 def performance_schema PRIMARY performance_schema session_status
169 def performance_schema PRIMARY performance_schema session_variables
170 def performance_schema PRIMARY performance_schema setup_actors
171 def performance_schema PRIMARY performance_schema setup_consumers
172 def performance_schema PRIMARY performance_schema setup_instruments
173 def performance_schema OBJECT performance_schema setup_objects
174 def performance_schema PRIMARY performance_schema setup_threads
175 def performance_schema PRIMARY performance_schema socket_instances
176 def performance_schema PRIMARY performance_schema socket_summary_by_event_name
177 def performance_schema PRIMARY performance_schema socket_summary_by_instance
178 def performance_schema ACCOUNT performance_schema status_by_account
179 def performance_schema HOST performance_schema status_by_host
180 def performance_schema PRIMARY performance_schema status_by_thread
181 def performance_schema USER performance_schema status_by_user
182 def performance_schema PRIMARY performance_schema table_handles
183 def performance_schema OBJECT performance_schema table_io_waits_summary_by_index_usage
184 def performance_schema OBJECT performance_schema table_io_waits_summary_by_table
185 def performance_schema OBJECT performance_schema table_lock_waits_summary_by_table
186 def performance_schema PRIMARY performance_schema threads
187 def performance_schema PRIMARY performance_schema user_defined_functions
188 def performance_schema PRIMARY performance_schema user_variables_by_thread
189 def performance_schema USER performance_schema users
190 def performance_schema PRIMARY performance_schema variables_by_thread
191 def sys PRIMARY sys sys_config
192 #########################################################################################
193 # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information
194 #########################################################################################
195 DROP DATABASE IF EXISTS db_datadict;
196 CREATE DATABASE db_datadict;
197 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
198 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
199 ENGINE = <some_engine_type>;
200 CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1);
201 CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3);
202 CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
203 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
204 ENGINE = <some_engine_type>;
205 DROP USER 'testuser1'@'localhost';
206 CREATE USER 'testuser1'@'localhost';
207 GRANT SELECT(f5) ON db_datadict.t1 TO 'testuser1'@'localhost';
208 SHOW GRANTS FOR 'testuser1'@'localhost';
209 Grants for testuser1@localhost
210 GRANT USAGE ON *.* TO `testuser1`@`localhost`
211 GRANT SELECT (`f5`) ON `db_datadict`.`t1` TO `testuser1`@`localhost`
212 SELECT * FROM information_schema.table_constraints
213 WHERE table_schema = 'db_datadict'
214 ORDER BY table_schema,table_name, constraint_name;
215 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
216 def db_datadict my_idx1 db_datadict t1 UNIQUE YES
217 def db_datadict my_idx2 db_datadict t1 UNIQUE YES
218 def db_datadict PRIMARY db_datadict t1 PRIMARY KEY YES
219 def db_datadict PRIMARY db_datadict t2 PRIMARY KEY YES
220 SHOW INDEXES FROM db_datadict.t1;
221 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
222 t1 0 PRIMARY 1 f1 ### ### ### ### ### ### ### YES NULL
223 t1 0 PRIMARY 2 f2 ### ### ### ### ### ### ### YES NULL
224 t1 0 my_idx1 1 f6 ### ### ### ### ### ### ### YES NULL
225 t1 0 my_idx1 2 f1 ### ### ### ### ### ### ### YES NULL
226 t1 0 my_idx2 1 f3 ### ### ### ### ### ### ### YES NULL
227 SHOW INDEXES FROM db_datadict.t2;
228 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
229 t2 0 PRIMARY 1 f1 ### ### ### ### ### ### ### YES NULL
230 t2 0 PRIMARY 2 f2 ### ### ### ### ### ### ### YES NULL
231 # Establish connection testuser1 (user=testuser1)
232 SHOW GRANTS FOR 'testuser1'@'localhost';
233 Grants for testuser1@localhost
234 GRANT USAGE ON *.* TO `testuser1`@`localhost`
235 GRANT SELECT (`f5`) ON `db_datadict`.`t1` TO `testuser1`@`localhost`
236 SELECT * FROM information_schema.table_constraints
237 WHERE table_schema = 'db_datadict'
238 ORDER BY table_schema,table_name, constraint_name;
239 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
240 def db_datadict my_idx1 db_datadict t1 UNIQUE YES
241 def db_datadict my_idx2 db_datadict t1 UNIQUE YES
242 def db_datadict PRIMARY db_datadict t1 PRIMARY KEY YES
243 SHOW INDEXES FROM db_datadict.t1;
244 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
245 t1 0 PRIMARY 1 f1 ### ### ### ### ### ### ### YES NULL
246 t1 0 PRIMARY 2 f2 ### ### ### ### ### ### ### YES NULL
247 t1 0 my_idx1 1 f6 ### ### ### ### ### ### ### YES NULL
248 t1 0 my_idx1 2 f1 ### ### ### ### ### ### ### YES NULL
249 t1 0 my_idx2 1 f3 ### ### ### ### ### ### ### YES NULL
250 SHOW INDEXES FROM db_datadict.t2;
251 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 't2'
252 # Switch to connection default and close connection testuser1
253 DROP USER 'testuser1'@'localhost';
254 DROP DATABASE db_datadict;
255 #########################################################################################
256 # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS modifications
257 #########################################################################################
258 DROP DATABASE IF EXISTS db_datadict;
259 DROP TABLE IF EXISTS test.t1_my_table;
260 CREATE DATABASE db_datadict;
261 SELECT table_name FROM information_schema.table_constraints
262 WHERE table_name LIKE 't1_my_table%';
263 TABLE_NAME
264 CREATE TABLE test.t1_my_table
265 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
266 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
267 ENGINE = <engine_type>;
268 SELECT constraint_name, table_schema, table_name, constraint_type
269 FROM information_schema.table_constraints
270 WHERE table_name = 't1_my_table';
271 CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
272 PRIMARY test t1_my_table PRIMARY KEY
273 SELECT table_name FROM information_schema.table_constraints
274 WHERE table_name LIKE 't1_my_table%';
275 TABLE_NAME
276 t1_my_table
277 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
278 SELECT table_name FROM information_schema.table_constraints
279 WHERE table_name LIKE 't1_my_table%';
280 TABLE_NAME
281 t1_my_tablex
282 SELECT table_schema,table_name FROM information_schema.table_constraints
283 WHERE table_name = 't1_my_tablex';
284 TABLE_SCHEMA TABLE_NAME
285 test t1_my_tablex
286 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
287 SELECT table_schema,table_name FROM information_schema.table_constraints
288 WHERE table_name = 't1_my_tablex';
289 TABLE_SCHEMA TABLE_NAME
290 db_datadict t1_my_tablex
291 SELECT constraint_schema, constraint_name, table_schema,
292 table_name, constraint_type
293 FROM information_schema.table_constraints
294 WHERE table_name = 't1_my_tablex'
295 ORDER BY table_schema,table_name, constraint_name;
296 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
297 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
298 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
299 SELECT constraint_schema, constraint_name, table_schema,
300 table_name, constraint_type
301 FROM information_schema.table_constraints
302 WHERE table_name = 't1_my_tablex'
303 ORDER BY table_schema,table_name, constraint_name;
304 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
305 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
306 DROP INDEX f2 ON db_datadict.t1_my_tablex;
307 SELECT constraint_schema, constraint_name, table_schema,
308 table_name, constraint_type
309 FROM information_schema.table_constraints
310 WHERE table_name = 't1_my_tablex'
311 ORDER BY table_schema,table_name, constraint_name;
312 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
313 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
314 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
315 SELECT constraint_schema, constraint_name, table_schema,
316 table_name, constraint_type
317 FROM information_schema.table_constraints
318 WHERE table_name = 't1_my_tablex'
319 ORDER BY table_schema,table_name, constraint_name;
320 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
321 db_datadict f2 db_datadict t1_my_tablex UNIQUE
322 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
323 DROP INDEX f2 ON db_datadict.t1_my_tablex;
324 SELECT constraint_schema, constraint_name, table_schema,
325 table_name, constraint_type
326 FROM information_schema.table_constraints
327 WHERE table_name = 't1_my_tablex'
328 ORDER BY table_schema,table_name, constraint_name;
329 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
330 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
331 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,f1);
332 SELECT constraint_schema, constraint_name, table_schema,
333 table_name, constraint_type
334 FROM information_schema.table_constraints
335 WHERE table_name = 't1_my_tablex'
336 ORDER BY table_schema,table_name, constraint_name;
337 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
338 db_datadict my_idx db_datadict t1_my_tablex UNIQUE
339 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
340 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
341 SELECT constraint_schema, constraint_name, table_schema,
342 table_name, constraint_type
343 FROM information_schema.table_constraints
344 WHERE table_name = 't1_my_tablex'
345 ORDER BY table_schema,table_name, constraint_name;
346 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
347 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
348 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
349 SELECT constraint_schema, constraint_name, table_schema,
350 table_name, constraint_type
351 FROM information_schema.table_constraints
352 WHERE table_name = 't1_my_tablex'
353 ORDER BY table_schema,table_name, constraint_name;
354 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
355 db_datadict my_idx db_datadict t1_my_tablex UNIQUE
356 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
357 SELECT constraint_schema, constraint_name, table_schema,
358 table_name, constraint_type
359 FROM information_schema.table_constraints
360 WHERE table_name = 't1_my_tablex'
361 ORDER BY table_schema,table_name, constraint_name;
362 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
363 db_datadict my_idx db_datadict t1_my_tablex UNIQUE
364 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
365 ALTER TABLE db_datadict.t1_my_tablex
366 DROP COLUMN f2;
367 SELECT constraint_schema, constraint_name, table_schema,
368 table_name, constraint_type
369 FROM information_schema.table_constraints
370 WHERE table_name = 't1_my_tablex'
371 ORDER BY table_schema,table_name, constraint_name;
372 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
373 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
374 SELECT table_name
375 FROM information_schema.table_constraints
376 WHERE table_name = 't1_my_tablex';
377 TABLE_NAME
378 t1_my_tablex
379 DROP TABLE db_datadict.t1_my_tablex;
380 SELECT table_name
381 FROM information_schema.table_constraints
382 WHERE table_name = 't1_my_tablex';
383 TABLE_NAME
384 SELECT table_name FROM information_schema.table_constraints
385 WHERE table_name = 't1_my_tablex';
386 TABLE_NAME
387 CREATE TABLE db_datadict.t1_my_tablex
388 ENGINE = <engine_type> AS
389 SELECT 1 AS f1;
390 SELECT table_name FROM information_schema.table_constraints
391 WHERE table_name = 't1_my_tablex';
392 TABLE_NAME
393 ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
394 SELECT table_name FROM information_schema.table_constraints
395 WHERE table_name = 't1_my_tablex';
396 TABLE_NAME
397 t1_my_tablex
398 SELECT table_name FROM information_schema.table_constraints
399 WHERE table_name = 't1_my_tablex';
400 TABLE_NAME
401 t1_my_tablex
402 DROP DATABASE db_datadict;
403 SELECT table_name FROM information_schema.table_constraints
404 WHERE table_name = 't1_my_tablex';
405 TABLE_NAME
406 ########################################################################
407 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
408 # DDL on INFORMATION_SCHEMA tables are not supported
409 ########################################################################
410 DROP DATABASE IF EXISTS db_datadict;
411 DROP TABLE IF EXISTS db_datadict.t1;
412 CREATE DATABASE db_datadict;
413 CREATE TABLE db_datadict.t1 (f1 BIGINT, UNIQUE(f1))
414 ENGINE = <engine_type>;
415 INSERT INTO information_schema.table_constraints
416 (constraint_schema, constraint_name, table_name)
417 VALUES ( 'mysql', 'primary', 'db');
418 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
419 INSERT INTO information_schema.table_constraints
420 SELECT * FROM information_schema.table_constraints;
421 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
422 UPDATE information_schema.table_constraints
423 SET table_name = 'db1' WHERE constraint_name = 'primary';
424 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
425 DELETE FROM information_schema.table_constraints WHERE table_name = 't1';
426 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
427 TRUNCATE information_schema.table_constraints;
428 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
429 CREATE INDEX i3 ON information_schema.table_constraints(table_name);
430 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
431 ALTER TABLE information_schema.table_constraints ADD f1 INT;
432 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
433 DROP TABLE information_schema.table_constraints;
434 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
435 ALTER TABLE information_schema.table_constraints
436 RENAME db_datadict.table_constraints;
437 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
438 ALTER TABLE information_schema.table_constraints
439 RENAME information_schema.xtable_constraints;
440 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
441 DROP TABLE db_datadict.t1;
442 DROP DATABASE db_datadict;