Debian Codegraph @

mysql-test/suite/ndbcluster/information_schema_fk.result

raw
1 #
2 # basic, i_s.table_constraints, is.key_column_usage
3 #
4 create table product (
5 category int not null,
6 id int not null,
7 price decimal,
8 primary key(category, id)
9 ) engine=ndb;
10 create table customer (
11 id int not null,
12 primary key (id)
13 ) engine=ndb;
14 create table product_order (
15 no int not null auto_increment,
16 product_category int not null,
17 product_id int not null,
18 customer_id int not null,
19 primary key(no),
20 index (product_category, product_id),
21 constraint fk1 foreign key (product_category, product_id)
22 references product(category, id)
23 on update restrict on delete cascade,
24 index (customer_id),
25 constraint fk2 foreign key (customer_id) references customer(id)
26 ) engine=ndb;
27 create table emp (
28 id int primary key auto_increment,
29 manager int,
30 key (manager),
31 constraint emp_fk1 foreign key (manager) references emp (id)
32 ) engine=ndb;
33 select * from information_schema.table_constraints
34 where table_schema = 'test'
35 order by table_name, constraint_type, constraint_name;
36 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
37 def test PRIMARY test customer PRIMARY KEY YES
38 def test emp_fk1 test emp FOREIGN KEY YES
39 def test PRIMARY test emp PRIMARY KEY YES
40 def test PRIMARY test product PRIMARY KEY YES
41 def test fk1 test product_order FOREIGN KEY YES
42 def test fk2 test product_order FOREIGN KEY YES
43 def test PRIMARY test product_order PRIMARY KEY YES
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 COL ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_COL
52 customer.id 1 NULL
53 emp.id 1 NULL
54 emp.manager 1 1 emp.id
55 product.category 1 NULL
56 product.id 2 NULL
57 product_order.customer_id 1 1 customer.id
58 product_order.no 1 NULL
59 product_order.product_category 1 1 product.category
60 product_order.product_id 2 2 product.id
61 drop table product_order, customer, product, emp;
62 #
63 # databases, circular, i_s.referential_constraints
64 #
65 create database mydb1;
66 create database mydb2;
67 create table mydb1.t1 (
68 a1 int not null,
69 b1 int not null,
70 primary key using hash (a1),
71 unique key xb1 (b1)
72 ) engine=ndb;
73 create table mydb2.t2 (
74 a2 int not null,
75 b2 int not null,
76 primary key using hash (a2),
77 unique key xb2 (b2)
78 ) engine=ndb;
79 alter table mydb1.t1
80 add constraint fk_b1a1 foreign key (b1) references mydb1.t1 (a1),
81 add constraint fk_b1b2 foreign key (b1) references mydb2.t2 (b2);
82 alter table mydb2.t2
83 add constraint fk_b2a2 foreign key (b2) references mydb2.t2 (a2),
84 add constraint fk_b2b1 foreign key (b2) references mydb1.t1 (b1);
85 select *
86 from information_schema.referential_constraints
87 order by constraint_schema, constraint_name;
88 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
89 def mydb1 fk_b1a1 def mydb1 PRIMARY NONE NO ACTION NO ACTION t1 t1
90 def mydb1 fk_b1b2 def mydb2 xb2 NONE NO ACTION NO ACTION t1 t2
91 def mydb2 fk_b2a2 def mydb2 PRIMARY NONE NO ACTION NO ACTION t2 t2
92 def mydb2 fk_b2b1 def mydb1 xb1 NONE NO ACTION NO ACTION t2 t1
93 select *
94 from information_schema.table_constraints
95 where table_schema like 'mydb%'
96 order by table_name, constraint_type, constraint_name;
97 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
98 def mydb1 fk_b1a1 mydb1 t1 FOREIGN KEY YES
99 def mydb1 fk_b1b2 mydb1 t1 FOREIGN KEY YES
100 def mydb1 PRIMARY mydb1 t1 PRIMARY KEY YES
101 def mydb1 xb1 mydb1 t1 UNIQUE YES
102 def mydb2 fk_b2a2 mydb2 t2 FOREIGN KEY YES
103 def mydb2 fk_b2b1 mydb2 t2 FOREIGN KEY YES
104 def mydb2 PRIMARY mydb2 t2 PRIMARY KEY YES
105 def mydb2 xb2 mydb2 t2 UNIQUE YES
106 select *
107 from information_schema.key_column_usage
108 where table_schema like 'mydb%'
109 order by constraint_schema, constraint_name;
110 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
111 def mydb1 fk_b1a1 def mydb1 t1 b1 1 1 mydb1 t1 a1
112 def mydb1 fk_b1b2 def mydb1 t1 b1 1 1 mydb2 t2 b2
113 def mydb1 PRIMARY def mydb1 t1 a1 1 NULL NULL NULL NULL
114 def mydb1 xb1 def mydb1 t1 b1 1 NULL NULL NULL NULL
115 def mydb2 fk_b2a2 def mydb2 t2 b2 1 1 mydb2 t2 a2
116 def mydb2 fk_b2b1 def mydb2 t2 b2 1 1 mydb1 t1 b1
117 def mydb2 PRIMARY def mydb2 t2 a2 1 NULL NULL NULL NULL
118 def mydb2 xb2 def mydb2 t2 b2 1 NULL NULL NULL NULL
119 alter table mydb1.t1
120 drop foreign key fk_b1a1,
121 drop foreign key fk_b1b2;
122 alter table mydb2.t2
123 drop foreign key fk_b2a2,
124 drop foreign key fk_b2b1;
125 drop table mydb1.t1;
126 drop table mydb2.t2;
127 drop database mydb1;
128 drop database mydb2;