is_table_constraints.result 15.4 KB
Newer Older
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
SHOW TABLES FROM information_schema LIKE 'TABLE_CONSTRAINTS';
Tables_in_information_schema (TABLE_CONSTRAINTS)
TABLE_CONSTRAINTS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.TABLE_CONSTRAINTS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLE_CONSTRAINTS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.TABLE_CONSTRAINTS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.TABLE_CONSTRAINTS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout
#########################################################################
DESCRIBE          information_schema.TABLE_CONSTRAINTS;
Field	Type	Null	Key	Default	Extra
31
CONSTRAINT_CATALOG	varchar(512)	NO			
32 33 34 35 36 37 38 39
CONSTRAINT_SCHEMA	varchar(64)	NO			
CONSTRAINT_NAME	varchar(64)	NO			
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
CONSTRAINT_TYPE	varchar(64)	NO			
SHOW CREATE TABLE information_schema.TABLE_CONSTRAINTS;
Table	Create Table
TABLE_CONSTRAINTS	CREATE TEMPORARY TABLE `TABLE_CONSTRAINTS` (
40
  `CONSTRAINT_CATALOG` varchar(512) NOT NULL DEFAULT '',
unknown's avatar
unknown committed
41 42 43 44 45
  `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_TYPE` varchar(64) NOT NULL DEFAULT ''
46 47 48
) ENGINE=MEMORY DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.TABLE_CONSTRAINTS;
Field	Type	Null	Key	Default	Extra
49
CONSTRAINT_CATALOG	varchar(512)	NO			
50 51 52 53 54 55 56 57 58 59
CONSTRAINT_SCHEMA	varchar(64)	NO			
CONSTRAINT_NAME	varchar(64)	NO			
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
CONSTRAINT_TYPE	varchar(64)	NO			
SELECT constraint_catalog, constraint_schema, constraint_name,
table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_catalog IS NOT NULL;
constraint_catalog	constraint_schema	constraint_name	table_schema	table_name
60
def	mysql	PRIMARY	mysql	column_stats
61 62 63 64 65 66 67 68 69 70 71 72
def	mysql	PRIMARY	mysql	columns_priv
def	mysql	PRIMARY	mysql	db
def	mysql	PRIMARY	mysql	event
def	mysql	PRIMARY	mysql	func
def	mysql	PRIMARY	mysql	help_category
def	mysql	name	mysql	help_category
def	mysql	PRIMARY	mysql	help_keyword
def	mysql	name	mysql	help_keyword
def	mysql	PRIMARY	mysql	help_relation
def	mysql	PRIMARY	mysql	help_topic
def	mysql	name	mysql	help_topic
def	mysql	PRIMARY	mysql	host
73
def	mysql	PRIMARY	mysql	index_stats
74 75 76 77
def	mysql	PRIMARY	mysql	ndb_binlog_index
def	mysql	PRIMARY	mysql	plugin
def	mysql	PRIMARY	mysql	proc
def	mysql	PRIMARY	mysql	procs_priv
78
def	mysql	PRIMARY	mysql	proxies_priv
79
def	mysql	PRIMARY	mysql	servers
80
def	mysql	PRIMARY	mysql	table_stats
81 82 83 84 85 86 87
def	mysql	PRIMARY	mysql	tables_priv
def	mysql	PRIMARY	mysql	time_zone
def	mysql	PRIMARY	mysql	time_zone_leap_second
def	mysql	PRIMARY	mysql	time_zone_name
def	mysql	PRIMARY	mysql	time_zone_transition
def	mysql	PRIMARY	mysql	time_zone_transition_type
def	mysql	PRIMARY	mysql	user
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111
#########################################################################################
# Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information
#########################################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
ENGINE = <some_engine_type>;
CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1);
CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3);
CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
ENGINE = <some_engine_type>;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT SELECT(f5) ON db_datadict.t1 TO 'testuser1'@'localhost';
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT SELECT (f5) ON `db_datadict`.`t1` TO 'testuser1'@'localhost'
SELECT * FROM information_schema.table_constraints
WHERE table_schema = 'db_datadict'
ORDER BY table_schema,table_name, constraint_name;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
112 113 114 115
def	db_datadict	my_idx1	db_datadict	t1	UNIQUE
def	db_datadict	my_idx2	db_datadict	t1	UNIQUE
def	db_datadict	PRIMARY	db_datadict	t1	PRIMARY KEY
def	db_datadict	PRIMARY	db_datadict	t2	PRIMARY KEY
116
SHOW INDEXES FROM db_datadict.t1;
117 118 119 120 121 122
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	f1	###	###	###	###	###	###	###	
t1	0	PRIMARY	2	f2	###	###	###	###	###	###	###	
t1	0	my_idx1	1	f6	###	###	###	###	###	###	###	
t1	0	my_idx1	2	f1	###	###	###	###	###	###	###	
t1	0	my_idx2	1	f3	###	###	###	###	###	###	###	
123
SHOW INDEXES FROM db_datadict.t2;
124 125 126
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t2	0	PRIMARY	1	f1	###	###	###	###	###	###	###	
t2	0	PRIMARY	2	f2	###	###	###	###	###	###	###	
127 128 129 130 131 132 133 134 135
# Establish connection testuser1 (user=testuser1)
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT SELECT (f5) ON `db_datadict`.`t1` TO 'testuser1'@'localhost'
SELECT * FROM information_schema.table_constraints
WHERE table_schema = 'db_datadict'
ORDER BY table_schema,table_name, constraint_name;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
136 137 138
def	db_datadict	my_idx1	db_datadict	t1	UNIQUE
def	db_datadict	my_idx2	db_datadict	t1	UNIQUE
def	db_datadict	PRIMARY	db_datadict	t1	PRIMARY KEY
139
SHOW INDEXES FROM db_datadict.t1;
140 141 142 143 144 145
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	f1	###	###	###	###	###	###	###	
t1	0	PRIMARY	2	f2	###	###	###	###	###	###	###	
t1	0	my_idx1	1	f6	###	###	###	###	###	###	###	
t1	0	my_idx1	2	f1	###	###	###	###	###	###	###	
t1	0	my_idx2	1	f3	###	###	###	###	###	###	###	
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338
SHOW INDEXES FROM db_datadict.t2;
ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 't2'
# Switch to connection default and close connection testuser1
DROP USER 'testuser1'@'localhost';
DROP DATABASE db_datadict;
#########################################################################################
# Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS modifications
#########################################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP TABLE IF EXISTS test.t1_my_table;
CREATE DATABASE db_datadict;
SELECT table_name FROM information_schema.table_constraints
WHERE table_name LIKE 't1_my_table%';
table_name
CREATE TABLE test.t1_my_table
(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
ENGINE = <engine_type>;
SELECT constraint_name, table_schema, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_table';
constraint_name	table_schema	table_name	constraint_type
PRIMARY	test	t1_my_table	PRIMARY KEY
SELECT table_name FROM information_schema.table_constraints
WHERE table_name LIKE 't1_my_table%';
table_name
t1_my_table
RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
SELECT table_name FROM information_schema.table_constraints
WHERE table_name LIKE 't1_my_table%';
table_name
t1_my_tablex
SELECT table_schema,table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_schema	table_name
test	t1_my_tablex
RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
SELECT table_schema,table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_schema	table_name
db_datadict	t1_my_tablex
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
DROP INDEX f2 ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	f2	db_datadict	t1_my_tablex	UNIQUE
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
DROP INDEX f2 ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,f1);
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	my_idx	db_datadict	t1_my_tablex	UNIQUE
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
DROP INDEX my_idx ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	my_idx	db_datadict	t1_my_tablex	UNIQUE
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	my_idx	db_datadict	t1_my_tablex	UNIQUE
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
ALTER TABLE db_datadict.t1_my_tablex
DROP COLUMN f2;
SELECT constraint_schema, constraint_name, table_schema,
table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex'
ORDER BY table_schema,table_name, constraint_name;
constraint_schema	constraint_name	table_schema	table_name	constraint_type
db_datadict	PRIMARY	db_datadict	t1_my_tablex	PRIMARY KEY
SELECT table_name
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
DROP TABLE db_datadict.t1_my_tablex;
SELECT table_name
FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
SELECT table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
CREATE TABLE db_datadict.t1_my_tablex
ENGINE = <engine_type> AS
SELECT 1 AS f1;
SELECT table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
SELECT table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
SELECT table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.table_constraints
WHERE table_name = 't1_my_tablex';
table_name
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA tables are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP TABLE IF EXISTS db_datadict.t1;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT, UNIQUE(f1))
ENGINE = <engine_type>;
INSERT INTO information_schema.table_constraints
(constraint_schema, constraint_name, table_name)
VALUES (          'mysql',       'primary',       'db');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.table_constraints
SELECT * FROM information_schema.table_constraints;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.table_constraints
SET  table_name = 'db1' WHERE constraint_name = 'primary';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.table_constraints WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.table_constraints;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i3 ON information_schema.table_constraints(table_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.table_constraints ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.table_constraints;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.table_constraints
RENAME db_datadict.table_constraints;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.table_constraints
RENAME information_schema.xtable_constraints;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE db_datadict.t1;
DROP DATABASE db_datadict;