~drizzle-trunk/drizzle/development

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;