1# engine=innodb 2create table product ( 3category int not null, 4id int not null, 5price decimal, 6primary key(category, id)) 7engine=innodb; 8create table customer ( 9id int not null, 10primary key (id)) 11engine=innodb; 12create table product_order ( 13no int not null auto_increment, 14product_category int not null, 15product_id int not null, 16customer_id int not null, 17primary key(no), 18index (product_category, product_id), 19foreign key fk1 (product_category, product_id) references product(category, id) 20on update restrict on delete cascade, 21index (customer_id), 22foreign key fk2 (customer_id) references customer(id)) 23engine=innodb; 24create table emp ( 25id int primary key auto_increment, 26manager int, 27key (manager), 28foreign key fk1 (manager) references emp (id)) 29engine=innodb; 30select * 31from information_schema.table_constraints 32where table_schema = 'test' 33order by table_name, constraint_type, constraint_name; 34CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 35def test PRIMARY test customer PRIMARY KEY 36def test emp_ibfk_1 test emp FOREIGN KEY 37def test PRIMARY test emp PRIMARY KEY 38def test PRIMARY test product PRIMARY KEY 39def test product_order_ibfk_1 test product_order FOREIGN KEY 40def test product_order_ibfk_2 test product_order FOREIGN KEY 41def test PRIMARY test product_order PRIMARY KEY 42COL ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_COL 43customer.id 1 NULL 44emp.id 1 NULL 45emp.manager 1 1 emp.id 46product.category 1 NULL 47product.id 2 NULL 48product_order.customer_id 1 1 customer.id 49product_order.no 1 NULL 50product_order.product_category 1 1 product.category 51product_order.product_id 2 2 product.id 52drop table product_order, customer, product, emp; 53create database mydb1; 54create database mydb2; 55create table mydb1.t1 ( 56a1 int not null, 57b1 int not null, 58primary key using hash (a1), 59unique key xb1 (b1) 60) engine=innodb; 61create table mydb2.t2 ( 62a2 int not null, 63b2 int not null, 64primary key using hash (a2), 65unique key xb2 (b2) 66) engine=innodb; 67alter table mydb1.t1 68add constraint fk_b1a1 foreign key (b1) references mydb1.t1 (a1), 69add constraint fk_b1b2 foreign key (b1) references mydb2.t2 (b2); 70alter table mydb2.t2 71add constraint fk_b2a2 foreign key (b2) references mydb2.t2 (a2), 72add constraint fk_b2b1 foreign key (b2) references mydb1.t1 (b1); 73select * 74from information_schema.referential_constraints 75order by constraint_schema, constraint_name; 76CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME 77def mydb1 fk_b1a1 def mydb1 PRIMARY NONE RESTRICT RESTRICT t1 t1 78def mydb1 fk_b1b2 def mydb2 xb2 NONE RESTRICT RESTRICT t1 t2 79def mydb2 fk_b2a2 def mydb2 PRIMARY NONE RESTRICT RESTRICT t2 t2 80def mydb2 fk_b2b1 def mydb1 xb1 NONE RESTRICT RESTRICT t2 t1 81select * 82from information_schema.table_constraints 83where table_schema like 'mydb%' 84order by table_name, constraint_type, constraint_name; 85CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 86def mydb1 fk_b1a1 mydb1 t1 FOREIGN KEY 87def mydb1 fk_b1b2 mydb1 t1 FOREIGN KEY 88def mydb1 PRIMARY mydb1 t1 PRIMARY KEY 89def mydb1 xb1 mydb1 t1 UNIQUE 90def mydb2 fk_b2a2 mydb2 t2 FOREIGN KEY 91def mydb2 fk_b2b1 mydb2 t2 FOREIGN KEY 92def mydb2 PRIMARY mydb2 t2 PRIMARY KEY 93def mydb2 xb2 mydb2 t2 UNIQUE 94select * 95from information_schema.key_column_usage 96where table_schema like 'mydb%' 97order by constraint_schema, constraint_name; 98CONSTRAINT_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 99def mydb1 fk_b1a1 def mydb1 t1 b1 1 1 mydb1 t1 a1 100def mydb1 fk_b1b2 def mydb1 t1 b1 1 1 mydb2 t2 b2 101def mydb1 PRIMARY def mydb1 t1 a1 1 NULL NULL NULL NULL 102def mydb1 xb1 def mydb1 t1 b1 1 NULL NULL NULL NULL 103def mydb2 fk_b2a2 def mydb2 t2 b2 1 1 mydb2 t2 a2 104def mydb2 fk_b2b1 def mydb2 t2 b2 1 1 mydb1 t1 b1 105def mydb2 PRIMARY def mydb2 t2 a2 1 NULL NULL NULL NULL 106def mydb2 xb2 def mydb2 t2 b2 1 NULL NULL NULL NULL 107alter table mydb1.t1 108drop foreign key fk_b1a1, 109drop foreign key fk_b1b2; 110alter table mydb2.t2 111drop foreign key fk_b2a2, 112drop foreign key fk_b2b1; 113drop table mydb1.t1; 114drop table mydb2.t2; 115drop database mydb1; 116drop database mydb2; 117# engine=ndb 118create table product ( 119category int not null, 120id int not null, 121price decimal, 122primary key(category, id)) 123engine=ndb; 124create table customer ( 125id int not null, 126primary key (id)) 127engine=ndb; 128create table product_order ( 129no int not null auto_increment, 130product_category int not null, 131product_id int not null, 132customer_id int not null, 133primary key(no), 134index (product_category, product_id), 135foreign key fk1 (product_category, product_id) references product(category, id) 136on update restrict on delete cascade, 137index (customer_id), 138foreign key fk2 (customer_id) references customer(id)) 139engine=ndb; 140create table emp ( 141id int primary key auto_increment, 142manager int, 143key (manager), 144foreign key fk1 (manager) references emp (id)) 145engine=ndb; 146select * 147from information_schema.table_constraints 148where table_schema = 'test' 149order by table_name, constraint_type, constraint_name; 150CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 151def test PRIMARY test customer PRIMARY KEY 152def test fk1 test emp FOREIGN KEY 153def test PRIMARY test emp PRIMARY KEY 154def test PRIMARY test product PRIMARY KEY 155def test fk1 test product_order FOREIGN KEY 156def test fk2 test product_order FOREIGN KEY 157def test PRIMARY test product_order PRIMARY KEY 158COL ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_COL 159customer.id 1 NULL 160emp.id 1 NULL 161emp.manager 1 1 emp.id 162product.category 1 NULL 163product.id 2 NULL 164product_order.customer_id 1 1 customer.id 165product_order.no 1 NULL 166product_order.product_category 1 1 product.category 167product_order.product_id 2 2 product.id 168drop table product_order, customer, product, emp; 169create database mydb1; 170create database mydb2; 171create table mydb1.t1 ( 172a1 int not null, 173b1 int not null, 174primary key using hash (a1), 175unique key xb1 (b1) 176) engine=ndb; 177create table mydb2.t2 ( 178a2 int not null, 179b2 int not null, 180primary key using hash (a2), 181unique key xb2 (b2) 182) engine=ndb; 183alter table mydb1.t1 184add constraint fk_b1a1 foreign key (b1) references mydb1.t1 (a1), 185add constraint fk_b1b2 foreign key (b1) references mydb2.t2 (b2); 186alter table mydb2.t2 187add constraint fk_b2a2 foreign key (b2) references mydb2.t2 (a2), 188add constraint fk_b2b1 foreign key (b2) references mydb1.t1 (b1); 189select * 190from information_schema.referential_constraints 191order by constraint_schema, constraint_name; 192CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME 193def mydb1 fk_b1a1 def mydb1 PRIMARY NONE NO ACTION NO ACTION t1 t1 194def mydb1 fk_b1b2 def mydb2 xb2$unique NONE NO ACTION NO ACTION t1 t2 195def mydb2 fk_b2a2 def mydb2 PRIMARY NONE NO ACTION NO ACTION t2 t2 196def mydb2 fk_b2b1 def mydb1 xb1$unique NONE NO ACTION NO ACTION t2 t1 197select * 198from information_schema.table_constraints 199where table_schema like 'mydb%' 200order by table_name, constraint_type, constraint_name; 201CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 202def mydb1 fk_b1a1 mydb1 t1 FOREIGN KEY 203def mydb1 fk_b1b2 mydb1 t1 FOREIGN KEY 204def mydb1 PRIMARY mydb1 t1 PRIMARY KEY 205def mydb1 xb1 mydb1 t1 UNIQUE 206def mydb2 fk_b2a2 mydb2 t2 FOREIGN KEY 207def mydb2 fk_b2b1 mydb2 t2 FOREIGN KEY 208def mydb2 PRIMARY mydb2 t2 PRIMARY KEY 209def mydb2 xb2 mydb2 t2 UNIQUE 210select * 211from information_schema.key_column_usage 212where table_schema like 'mydb%' 213order by constraint_schema, constraint_name; 214CONSTRAINT_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 215def mydb1 fk_b1a1 def mydb1 t1 b1 1 1 mydb1 t1 a1 216def mydb1 fk_b1b2 def mydb1 t1 b1 1 1 mydb2 t2 b2 217def mydb1 PRIMARY def mydb1 t1 a1 1 NULL NULL NULL NULL 218def mydb1 xb1 def mydb1 t1 b1 1 NULL NULL NULL NULL 219def mydb2 fk_b2a2 def mydb2 t2 b2 1 1 mydb2 t2 a2 220def mydb2 fk_b2b1 def mydb2 t2 b2 1 1 mydb1 t1 b1 221def mydb2 PRIMARY def mydb2 t2 a2 1 NULL NULL NULL NULL 222def mydb2 xb2 def mydb2 t2 b2 1 NULL NULL NULL NULL 223alter table mydb1.t1 224drop foreign key fk_b1a1, 225drop foreign key fk_b1b2; 226alter table mydb2.t2 227drop foreign key fk_b2a2, 228drop foreign key fk_b2b1; 229drop table mydb1.t1; 230drop table mydb2.t2; 231drop database mydb1; 232drop database mydb2; 233