~drizzle-trunk/drizzle/development

CREATE SCHEMA test_constaints;

USE test_constaints;

CREATE TABLE t1(a1 INT NOT NULL,

a2 INT NOT NULL,

PRIMARY KEY(a1, a2));

CREATE TABLE t2(b1 INT,

b2 INT,

CONSTRAINT A1 FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2) ON UPDATE CASCADE ON DELETE NO ACTION);

SELECT CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 

WHERE CONSTRAINT_SCHEMA = 'test_constaints';

CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA        UNIQUE_CONSTRAINT_NAME  MATCH_OPTION    UPDATE_RULE     DELETE_RULE

A1      test_constaints         SIMPLE  CASCADE NO ACTION

SELECT CONSTRAINT_NAME, CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = 'test_constaints';

CONSTRAINT_NAME CHECK_CLAUSE

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE CONSTRAINT_SCHEMA = 'test_constaints';

TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     CONSTRAINT_CATALOG      CONSTRAINT_SCHEMA       CONSTRAINT_NAME

SELECT TABLE_NAME,CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

WHERE CONSTRAINT_SCHEMA = 'test_constaints';

TABLE_NAME      CONSTRAINT_NAME

DROP TABLE t2;

DROP TABLE t1;

DROP SCHEMA test_constaints;