~drizzle-trunk/drizzle/development

DROP TABLE IF EXISTS t1,t2,t3;

CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id, id),

FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE,

FOREIGN KEY (t1_id) REFERENCES t1(id)  ON UPDATE CASCADE) ENGINE=INNODB;

CREATE TABLE t3 (id INT PRIMARY KEY, t2_id INT, INDEX par_ind (t2_id),

FOREIGN KEY (id, t2_id) REFERENCES t2(t1_id, id)  ON DELETE CASCADE) ENGINE=INNODB;

select * from information_schema.TABLE_CONSTRAINTS where

TABLE_SCHEMA= "test";

CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE

NULL	test	PRIMARY	test	t1	PRIMARY KEY

NULL	test	PRIMARY	test	t2	PRIMARY KEY

NULL	test	t2_ibfk_1	test	t2	FOREIGN KEY

NULL	test	t2_ibfk_2	test	t2	FOREIGN KEY

NULL	test	PRIMARY	test	t3	PRIMARY KEY

NULL	test	t3_ibfk_1	test	t3	FOREIGN KEY

select * from information_schema.KEY_COLUMN_USAGE where

TABLE_SCHEMA= "test";

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

NULL	test	PRIMARY	NULL	test	t1	id	1	NULL	NULL	NULL	NULL

NULL	test	PRIMARY	NULL	test	t2	id	1	NULL	NULL	NULL	NULL

NULL	test	t2_ibfk_1	NULL	test	t2	t1_id	1	1	test	t1	id

NULL	test	t2_ibfk_2	NULL	test	t2	t1_id	1	1	test	t1	id

NULL	test	PRIMARY	NULL	test	t3	id	1	NULL	NULL	NULL	NULL

NULL	test	t3_ibfk_1	NULL	test	t3	id	1	1	test	t2	t1_id

NULL	test	t3_ibfk_1	NULL	test	t3	t2_id	2	2	test	t2	id

drop table t3, t2, t1;

CREATE TABLE t1(a1 INT NOT NULL, a2 INT NOT NULL,

PRIMARY KEY(a1, a2)) ENGINE=INNODB;

CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2),

CONSTRAINT A1

FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2)

ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB;

CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2),

CONSTRAINT A2

FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2)

ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB;

CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2),

CONSTRAINT A3

FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2)

ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB;

CREATE TABLE t5(b1 INT, b2 INT, INDEX (b1, b2),

CONSTRAINT A4

FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2)

ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB;

select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE,

b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME,

MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME

from information_schema.TABLE_CONSTRAINTS a,

information_schema.REFERENTIAL_CONSTRAINTS b

where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and

a.CONSTRAINT_NAME = b.CONSTRAINT_NAME;

CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	REFERENCED_TABLE_NAME

test	t2	FOREIGN KEY	A1	test	PRIMARY	NONE	CASCADE	NO ACTION	t1

test	t3	FOREIGN KEY	A2	test	b1	NONE	SET NULL	RESTRICT	t2

test	t4	FOREIGN KEY	A3	test	t3_indx	NONE	NO ACTION	SET NULL	t3

test	t5	FOREIGN KEY	A4	test	t4_ukey	NONE	RESTRICT	CASCADE	t4

drop tables t5, t4, t3, t2, t1;

create database `db-1`;

use `db-1`;

create table `t-2` (

id int(10) unsigned not null auto_increment,

primary key (id)

) engine=innodb;

create table `t-1` (

id int(10) unsigned not null auto_increment,

idtype int(10) unsigned not null,

primary key (id),

key fk_t1_1 (idtype),

constraint fk_t1_1 foreign key (idtype) references `t-2` (id)

) engine=innodb;

use test;

select referenced_table_schema, referenced_table_name 

from information_schema.key_column_usage

where constraint_schema = 'db-1';

referenced_table_schema	referenced_table_name

NULL	NULL

db-1	t-2

NULL	NULL

drop database `db-1`;

create table t1(id int primary key) engine = Innodb;

create table t2(pid int, foreign key (pid) references t1(id)) engine = Innodb;

set foreign_key_checks = 0;

drop table t1;

select UNIQUE_CONSTRAINT_NAME

from information_schema.referential_constraints

where constraint_schema = schema();

UNIQUE_CONSTRAINT_NAME

NULL

drop table t2;

set foreign_key_checks = 1;