Debian Codegraph @

mysql-test/suite/ndbcluster/information_schema_fk.test

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