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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
#
# basic, i_s.table_constraints, is.key_column_usage
#
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;
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
def test PRIMARY test customer PRIMARY KEY YES
def test emp_fk1 test emp FOREIGN KEY YES
def test PRIMARY test emp PRIMARY KEY YES
def test PRIMARY test product PRIMARY KEY YES
def test fk1 test product_order FOREIGN KEY YES
def test fk2 test product_order FOREIGN KEY YES
def test PRIMARY test product_order PRIMARY KEY YES
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;
COL ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_COL
customer.id 1 NULL
emp.id 1 NULL
emp.manager 1 1 emp.id
product.category 1 NULL
product.id 2 NULL
product_order.customer_id 1 1 customer.id
product_order.no 1 NULL
product_order.product_category 1 1 product.category
product_order.product_id 2 2 product.id
drop table product_order, customer, product, emp;
#
# databases, circular, i_s.referential_constraints
#
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;
CONSTRAINT_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
def mydb1 fk_b1a1 def mydb1 PRIMARY NONE NO ACTION NO ACTION t1 t1
def mydb1 fk_b1b2 def mydb2 xb2 NONE NO ACTION NO ACTION t1 t2
def mydb2 fk_b2a2 def mydb2 PRIMARY NONE NO ACTION NO ACTION t2 t2
def mydb2 fk_b2b1 def mydb1 xb1 NONE NO ACTION NO ACTION t2 t1
select *
from information_schema.table_constraints
where table_schema like 'mydb%'
order by table_name, constraint_type, constraint_name;
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
def mydb1 fk_b1a1 mydb1 t1 FOREIGN KEY YES
def mydb1 fk_b1b2 mydb1 t1 FOREIGN KEY YES
def mydb1 PRIMARY mydb1 t1 PRIMARY KEY YES
def mydb1 xb1 mydb1 t1 UNIQUE YES
def mydb2 fk_b2a2 mydb2 t2 FOREIGN KEY YES
def mydb2 fk_b2b1 mydb2 t2 FOREIGN KEY YES
def mydb2 PRIMARY mydb2 t2 PRIMARY KEY YES
def mydb2 xb2 mydb2 t2 UNIQUE YES
select *
from information_schema.key_column_usage
where table_schema like 'mydb%'
order by constraint_schema, constraint_name;
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
def mydb1 fk_b1a1 def mydb1 t1 b1 1 1 mydb1 t1 a1
def mydb1 fk_b1b2 def mydb1 t1 b1 1 1 mydb2 t2 b2
def mydb1 PRIMARY def mydb1 t1 a1 1 NULL NULL NULL NULL
def mydb1 xb1 def mydb1 t1 b1 1 NULL NULL NULL NULL
def mydb2 fk_b2a2 def mydb2 t2 b2 1 1 mydb2 t2 a2
def mydb2 fk_b2b1 def mydb2 t2 b2 1 1 mydb1 t1 b1
def mydb2 PRIMARY def mydb2 t2 a2 1 NULL NULL NULL NULL
def mydb2 xb2 def mydb2 t2 b2 1 NULL NULL NULL NULL
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;
|
