Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_is.result

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