1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
CREATE TABLE `prim_key` (
`id` INT NOT NULL,
`val` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `fore_key0` (
`id` INT NOT NULL,
`id_ref0` INT DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_ref0` (`id_ref0`),
CONSTRAINT `fore_key0_ibfk_1` FOREIGN KEY (`id_ref0`) REFERENCES
`prim_key` (`id`)
);
CREATE TABLE `fore_key1` (
`id` INT NOT NULL,
`id_ref1` INT DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_ref1` (`id_ref1`),
CONSTRAINT `fore_key1_ibfk_1` FOREIGN KEY (`id_ref1`) REFERENCES
`prim_key` (`id`) ON UPDATE CASCADE
);
SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ORDINAL_POSITION
PRIMARY fore_key0 id 1
PRIMARY fore_key1 id 1
PRIMARY prim_key id 1
fore_key0_ibfk_1 fore_key0 id_ref0 1
fore_key1_ibfk_1 fore_key1 id_ref1 1
DROP TABLE fore_key1;
DROP TABLE fore_key0;
DROP TABLE prim_key;
CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
);
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2)
REFERENCES t1(s3)
);
SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ORDINAL_POSITION
CO t3 s2 1
PRIMARY t1 s3 1
DROP TABLE t3;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, c3 CHAR(5), c4 INT, c5 INT, constraint pk_t1 PRIMARY KEY (c5,c4));
CREATE TABLE T2 (tc1 INT NOT NULL, c1 INT NOT NULL, c2 INT NOT NULL, c5 CHAR(5), constraint fk_t2 FOREIGN KEY (c1,c2) REFERENCES t1 (c5,c4));
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME IN ('t1', 't2');
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ORDINAL_POSITION
PRIMARY t1 c4 2
PRIMARY t1 c5 1
fk_t2 t2 c1 1
fk_t2 t2 c2 2
DROP TABLE t2;
DROP TABLE t1;
|