1
DROP TABLE IF EXISTS t1,t2,t3;
2
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
3
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id, id),
4
FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE,
5
FOREIGN KEY (t1_id) REFERENCES t1(id) ON UPDATE CASCADE) ENGINE=INNODB;
6
CREATE TABLE t3 (id INT PRIMARY KEY, t2_id INT, INDEX par_ind (t2_id),
7
FOREIGN KEY (id, t2_id) REFERENCES t2(t1_id, id) ON DELETE CASCADE) ENGINE=INNODB;
8
select * from information_schema.TABLE_CONSTRAINTS where
10
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
11
NULL test PRIMARY test t1 PRIMARY KEY
12
NULL test PRIMARY test t2 PRIMARY KEY
13
NULL test t2_ibfk_1 test t2 FOREIGN KEY
14
NULL test t2_ibfk_2 test t2 FOREIGN KEY
15
NULL test PRIMARY test t3 PRIMARY KEY
16
NULL test t3_ibfk_1 test t3 FOREIGN KEY
17
select * from information_schema.KEY_COLUMN_USAGE where
19
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
20
NULL test PRIMARY NULL test t1 id 1 NULL NULL NULL NULL
21
NULL test PRIMARY NULL test t2 id 1 NULL NULL NULL NULL
22
NULL test t2_ibfk_1 NULL test t2 t1_id 1 1 test t1 id
23
NULL test t2_ibfk_2 NULL test t2 t1_id 1 1 test t1 id
24
NULL test PRIMARY NULL test t3 id 1 NULL NULL NULL NULL
25
NULL test t3_ibfk_1 NULL test t3 id 1 1 test t2 t1_id
26
NULL test t3_ibfk_1 NULL test t3 t2_id 2 2 test t2 id
27
drop table t3, t2, t1;
28
CREATE TABLE t1(a1 INT NOT NULL, a2 INT NOT NULL,
29
PRIMARY KEY(a1, a2)) ENGINE=INNODB;
30
CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2),
32
FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2)
33
ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB;
34
CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2),
36
FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2)
37
ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB;
38
CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2),
40
FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2)
41
ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB;
42
CREATE TABLE t5(b1 INT, b2 INT, INDEX (b1, b2),
44
FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2)
45
ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB;
46
select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE,
47
b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME,
48
MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME
49
from information_schema.TABLE_CONSTRAINTS a,
50
information_schema.REFERENTIAL_CONSTRAINTS b
51
where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
52
a.CONSTRAINT_NAME = b.CONSTRAINT_NAME;
53
CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE REFERENCED_TABLE_NAME
54
test t2 FOREIGN KEY A1 test PRIMARY NONE CASCADE NO ACTION t1
55
test t3 FOREIGN KEY A2 test b1 NONE SET NULL RESTRICT t2
56
test t4 FOREIGN KEY A3 test t3_indx NONE NO ACTION SET NULL t3
57
test t5 FOREIGN KEY A4 test t4_ukey NONE RESTRICT CASCADE t4
58
drop tables t5, t4, t3, t2, t1;
59
create database `db-1`;
62
id int(10) unsigned not null auto_increment,
66
id int(10) unsigned not null auto_increment,
67
idtype int(10) unsigned not null,
70
constraint fk_t1_1 foreign key (idtype) references `t-2` (id)
73
select referenced_table_schema, referenced_table_name
74
from information_schema.key_column_usage
75
where constraint_schema = 'db-1';
76
referenced_table_schema referenced_table_name
81
create table t1(id int primary key) engine = Innodb;
82
create table t2(pid int, foreign key (pid) references t1(id)) engine = Innodb;
83
set foreign_key_checks = 0;
85
select UNIQUE_CONSTRAINT_NAME
86
from information_schema.referential_constraints
87
where constraint_schema = schema();
88
UNIQUE_CONSTRAINT_NAME
91
set foreign_key_checks = 1;