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
|
--source include/have_ndb.inc
--echo #
--echo # basic, i_s.table_constraints, is.key_column_usage
--echo #
create table product (
category int not null,
id int not null,
price decimal,
primary key(category, id)
) engine=ndb;
create table customer (
id int not null,
primary key (id)
) engine=ndb;
create table product_order (
no int not null auto_increment,
product_category int not null,
product_id int not null,
customer_id int not null,
primary key(no),
index (product_category, product_id),
constraint fk1 foreign key (product_category, product_id)
references product(category, id)
on update restrict on delete cascade,
index (customer_id),
constraint fk2 foreign key (customer_id) references customer(id)
) engine=ndb;
create table emp (
id int primary key auto_increment,
manager int,
key (manager),
constraint emp_fk1 foreign key (manager) references emp (id)
) engine=ndb;
select * from information_schema.table_constraints
where table_schema = 'test'
order by table_name, constraint_type, constraint_name;
select CONCAT_WS('.', TABLE_NAME, COLUMN_NAME) as COL,
ORDINAL_POSITION,
POSITION_IN_UNIQUE_CONSTRAINT,
CONCAT_WS('.', REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME) as REFERENCED_COL
from information_schema.key_column_usage
where table_schema = 'test'
order by COL, ORDINAL_POSITION;
drop table product_order, customer, product, emp;
--echo #
--echo # databases, circular, i_s.referential_constraints
--echo #
create database mydb1;
create database mydb2;
create table mydb1.t1 (
a1 int not null,
b1 int not null,
primary key using hash (a1),
unique key xb1 (b1)
) engine=ndb;
create table mydb2.t2 (
a2 int not null,
b2 int not null,
primary key using hash (a2),
unique key xb2 (b2)
) engine=ndb;
alter table mydb1.t1
add constraint fk_b1a1 foreign key (b1) references mydb1.t1 (a1),
add constraint fk_b1b2 foreign key (b1) references mydb2.t2 (b2);
alter table mydb2.t2
add constraint fk_b2a2 foreign key (b2) references mydb2.t2 (a2),
add constraint fk_b2b1 foreign key (b2) references mydb1.t1 (b1);
select *
from information_schema.referential_constraints
order by constraint_schema, constraint_name;
select *
from information_schema.table_constraints
where table_schema like 'mydb%'
order by table_name, constraint_type, constraint_name;
select *
from information_schema.key_column_usage
where table_schema like 'mydb%'
order by constraint_schema, constraint_name;
alter table mydb1.t1
drop foreign key fk_b1a1,
drop foreign key fk_b1b2;
alter table mydb2.t2
drop foreign key fk_b2a2,
drop foreign key fk_b2b1;
drop table mydb1.t1;
drop table mydb2.t2;
drop database mydb1;
drop database mydb2;
|
