Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_is.inc

raw
1 eval
2 create table product (
3 category int not null,
4 id int not null,
5 price decimal,
6 primary key(category, id))
7 engine=$engine;
8
9 eval
10 create table customer (
11 id int not null,
12 primary key (id))
13 engine=$engine;
14
15 eval
16 create table product_order (
17 no int not null auto_increment,
18 product_category int not null,
19 product_id int not null,
20 customer_id int not null,
21 primary key(no),
22 index (product_category, product_id),
23 constraint fk1 foreign key (product_category, product_id)
24 references product(category, id)
25 on update restrict on delete cascade,
26 index (customer_id),
27 constraint fk2 foreign key (customer_id) references customer(id))
28 engine=$engine;
29
30 eval
31 create table emp (
32 id int primary key auto_increment,
33 manager int,
34 key (manager),
35 constraint emp_fk1 foreign key (manager) references emp (id))
36 engine=$engine;
37
38 select *
39 from information_schema.table_constraints
40 where table_schema = 'test'
41 order by table_name, constraint_type, constraint_name;
42
43 --disable_query_log
44 select CONCAT_WS('.', TABLE_NAME, COLUMN_NAME) as COL,
45 ORDINAL_POSITION,
46 POSITION_IN_UNIQUE_CONSTRAINT,
47 CONCAT_WS('.', REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME) as REFERENCED_COL
48 from information_schema.key_column_usage
49 where table_schema = 'test'
50 order by COL, ORDINAL_POSITION;
51 --enable_query_log
52
53 drop table product_order, customer, product, emp;
54
55 # databases, circular, i_s.referential_constraints
56
57 create database mydb1;
58 create database mydb2;
59
60 eval
61 create table mydb1.t1 (
62 a1 int not null,
63 b1 int not null,
64 primary key using hash (a1),
65 unique key xb1 (b1)
66 ) engine=$engine;
67
68 eval
69 create table mydb2.t2 (
70 a2 int not null,
71 b2 int not null,
72 primary key using hash (a2),
73 unique key xb2 (b2)
74 ) engine=$engine;
75
76 alter table mydb1.t1
77 add constraint fk_b1a1 foreign key (b1) references mydb1.t1 (a1),
78 add constraint fk_b1b2 foreign key (b1) references mydb2.t2 (b2);
79
80 alter table mydb2.t2
81 add constraint fk_b2a2 foreign key (b2) references mydb2.t2 (a2),
82 add constraint fk_b2b1 foreign key (b2) references mydb1.t1 (b1);
83
84 select *
85 from information_schema.referential_constraints
86 order by constraint_schema, constraint_name;
87
88 select *
89 from information_schema.table_constraints
90 where table_schema like 'mydb%'
91 order by table_name, constraint_type, constraint_name;
92
93 select *
94 from information_schema.key_column_usage
95 where table_schema like 'mydb%'
96 order by constraint_schema, constraint_name;
97
98 alter table mydb1.t1
99 drop foreign key fk_b1a1,
100 drop foreign key fk_b1b2;
101
102 alter table mydb2.t2
103 drop foreign key fk_b2a2,
104 drop foreign key fk_b2b1;
105
106 drop table mydb1.t1;
107 drop table mydb2.t2;
108
109 drop database mydb1;
110 drop database mydb2;